| 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: | Whole Thread | Raw Message | 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)
| 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? |