Subqueries and the optimizer
От | Dmitri Bichko |
---|---|
Тема | Subqueries and the optimizer |
Дата | |
Msg-id | 7A4ADADFC8AFF0478D47F63BEDD57CE30D0909@gpmail.gphq.genpathpharma.com обсуждение исходный текст |
Ответы |
Re: Subqueries and the optimizer
|
Список | 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
В списке pgsql-general по дате отправления: