From: | "Dmitri Bichko" <dbichko(at)genpathpharma(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Subqueries and the optimizer |
Date: | 2003-05-20 17:50:32 |
Message-ID: | 7A4ADADFC8AFF0478D47F63BEDD57CE30D0909@gpmail.gphq.genpathpharma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
Thanks,
Dmitri
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-05-20 17:52:41 | Re: sequence caches |
Previous Message | adeon | 2003-05-20 17:47:55 | How to deny user changing his own password? |