Thread: A join that should be very fast is taking a long time

I searched through mailing list archives but was unable to find full
coverage of this question -- my apologies if this is a reposted question.

I have two tables with about 9,000 records in each table.  Each table has an
index on the column track_id.

I am trying to do the following query:
"select * from track_category_track c, track_datafile d where
c.track_id='49682224EB2753AB0A262ACB00172E08' and d.track_id=c.track_id"

This takes more than 1 second to run, even though track_id is indexed in
both tables.  Furthermore, if I do
"select * from track_category_track c, track_datafile d where
c.track_id='49682224EB2753AB0A262ACB00172E08' and
d.track_id='49682224EB2753AB0A262ACB00172E08'"
 then the query runs in 20ms.

I also have a third table with track_id where track_id is the primary key,
and when I do the same join between that table and track_category_track or
track_datafile, the query executes in 20ms.  So the problem seems to be only
when I am joining these two tables.

Anyone have any idea why?  Thanks...

Nick

"Nick Ganju" <nganju@mixonic.com> writes:
> I am trying to do the following query:
> "select * from track_category_track c, track_datafile d where
> c.track_id='49682224EB2753AB0A262ACB00172E08' and d.track_id=c.track_id"

> This takes more than 1 second to run, even though track_id is indexed in
> both tables.

Have you vacuum analyzed lately?  What PG version is this?  What does
EXPLAIN show for the query plan?

            regards, tom lane