Subqueries and the optimizer

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

Responses

Browse pgsql-general by date

  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?