Re: Subqueries and the optimizer

Try:

SELECT blast_id FROM genes, ll_out_mm WHERE blast_batch_id = 2 AND
genes.locus_id = ll_out_mm.locus_id;

Using more recent versions of PostgreSQL, you can also write:

SELECT blast_id FROM genes JOIN ll_out_mm USING ( locus_id) WHERE blast_batch_id = 2;

-- Dean

Dmitri Bichko wrote on 2003-05-20 10:50:
>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly