query with subquery abnormally slow?

From: Oskar Liljeblad <osk(at)hem(dot)passagen(dot)se>
To: pgsql-sql(at)postgresql(dot)org
Subject: query with subquery abnormally slow?
Date: 1999-11-01 18:47:07
Message-ID: 19991101194707.A7034@oskar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I'm doing a SELECT query with a subquery on a table with 12K rows
but it is very slow (10 seconds+). The query looks like this:

select *
from items
where package in
(select package
from items
where ...blah...
group by package)

Where package is an indexed varchar field.

The subquery generates 50 rows, and completes quickly when I run
it alone. Now if I manually replace the subquery with these 50
strings - like

select *
from items
where package in
('r1', 'r2', 'r3', .., 'r50')

this new query completes in less than a second. The result
of the complete query is 500 rows.

Is this natural? Is there anything I can do to speed things up?
(Besides doing N+1 queries where N is the number of rows the
subquery would produce.)

Oskar Liljeblad (osk(at)hem(dot)passagen(dot)se)

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Oskar Liljeblad 1999-11-01 19:05:28 Re: [SQL] query with subquery abnormally slow?
Previous Message Darrin Crook 1999-11-01 14:46:35 Re: [SQL] auto datetime update on UPDATE?