Subqueries and the optimizer

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

Предыдущее
От: adeon
Дата:
Сообщение: How to deny user changing his own password?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: sequence caches