From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Dmitri Bichko <dbichko(at)genpathpharma(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Subqueries and the optimizer |
Date: | 2003-05-20 18:19:55 |
Message-ID: | 20030520111742.P70702-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 20 May 2003, Dmitri Bichko wrote:
> So, I have a table with an index:
>
> dev=# explain SELECT blast_id FROM genes WHERE blast_batch_id = 2 AND
> locus_id IN (1,2);
> QUERY PLAN
> ------------------------------------------------------------------------
> ------------------------------
> Index Scan using idx_genes_locus_id, idx_genes_locus_id on genes
> (cost=0.00..88.21 rows=14 width=4)
> Index Cond: ((locus_id = 1) OR (locus_id = 2))
> Filter: (blast_batch_id = 2)
>
> So far so good, but when I try it with a subquery:
>
> dev=# explain SELECT blast_id FROM genes WHERE blast_batch_id = 2 AND
> locus_id IN (SELECT locus_id FROM ll_out_mm);
> QUERY PLAN
> ------------------------------------------------------------------------
> Seq Scan on genes (cost=0.00..21414353.48 rows=11003 width=4)
> Filter: ((blast_batch_id = 2) AND (subplan))
> SubPlan
> -> Seq Scan on ll_out_mm (cost=0.00..1267.64 rows=59264 width=4)
>
>
> How can I nudge the optimizer in the direction of using the index in the
> second case as well? Or is it supposed to be doing this in this case.
In current stable versions, IN is not optimized terribly well. 7.4 will
do a much better job. Converting to a join or EXISTS may help in the
short term.
From | Date | Subject | |
---|---|---|---|
Next Message | Dean K. Gibson | 2003-05-20 18:27:33 | Re: Subqueries and the optimizer |
Previous Message | Rod Taylor | 2003-05-20 18:13:11 | Re: sequence caches |