Foreign Key search

babochan

Hi,

I have a problem on SQL performance on PostgreSQL 7.1.3.

I have following tables.

create table tMailContentsF (
ContentsCD int8 not null,
primary key (ContentsCD),
FolderCD int8,
CONSTRAINT tMailContentsF_FolderCD
FOREIGN KEY(FolderCD)
REFERENCES tMailFolderF(FolderCD)
ON UPDATE CASCADE
ON DELETE CASCADE,
:
:
)

create table tMailFolderF (
FolderCD int8 not null,
primary key (FolderCD),
FolderName text
:
:
)

When I excute following SQL, the speed is very slow.

 >explain select * from tMailContentsF where FolderCD = 1234;

So, I decided to create an index for FolderCD field.

 >create index fordercd_idx on tMailContentsF(FolderCD);
 >explain select * from tMailContentsF where FolderCD = '1234';

Since this is a foreign key, I might not need to create an index, however either ways doesn't work.

How can I speed up my SQL?

ultraslacker

You haven't given very useful data, such as the output of explain analyze, but most likely you need to vacuum analyze the table.

Read up on indexes - http://www.ca.postgresql.org/docs/aw_pgsql_book/node107.html

And you are using an older version...

[deleted]

Q: why do you care that running an 'explain' is slow?

babochan

Hi,

It was working, and I just didn't notice it. When I searched for FolderCD=1, the speed was slow, but whe I searched for FolderCD=111, it was fast. So, the speed was depend on data, I didn't notice it.

Thank you for your reply.

P.S.
I didn't care about Explain, I just want to show that I was using it to find out the speed. I was my mistake that I didn't post Explain result.

Sxooter

When you did the explain did you get seq scans or index scans?

Can you get it to use index scans in some cases? If not, you may not have a matching type for the foriegn key.

Postgresql does not automatically coerce types as readily as some other databases, so sometimes you need to coerce the type yourself.

Try two things. One is

where folderCD='111'

using ticks like above will force a type coercion, and you may find the performance is much better.

Also, on a join of say an int8 with an in4, you can cast them like so:

c1 join c2 on (c1.field1::int8 = c2.field2::int8)