Re: Why does the query planner use two full indexes, when a dedicated partial index exists?

От: Tom Lane
Тема: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
Дата: ,
Msg-id: 11997.1356018206@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill)
Список: pgsql-performance

Скрыть дерево обсуждения

Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
 Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Sergey Konoplev, )
  Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
   Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Sergey Konoplev, )
    Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
     Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Jeff Janes, )
      Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
       Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Tom Lane, )
      Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Tom Lane, )
       Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Jeff Janes, )
        Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Jeff Janes, )
 Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Tom Lane, )
  Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
  Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
   Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
    Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Jeff Janes, )
     Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
     Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?)  (Richard Neill, )
      Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?)  (John Rouillard, )
       Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?)  (Jeff Janes, )
      Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?)  (Jeff Janes, )
   Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Jeff Janes, )
    Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
     Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Jeff Janes, )
      Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
      Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
    Why does the query planner use two full indexes, when a dedicated partial index exists?  (Jeff Janes, )
     Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Richard Neill, )
      Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Tom Lane, )
      Re: Why does the query planner use two full indexes, when a dedicated partial index exists?  (Jeff Janes, )

Richard Neill <> writes:
> Also, I wonder whether it matters which order the indexes are created in?

IIRC, if the estimated costs of using two different indexes come out the
same (to within 1% or so), then the planner keeps the first-generated
path, which will result in preferring the index with smaller OID.  This
effect doesn't apply to your problem query though, since we can see from
the drop-experiments that the estimated costs are quite a bit different.

A more likely explanation if you see some effect that looks like order
dependency is that the more recently created index has accumulated less
bloat, and thus has a perfectly justifiable cost advantage.

            regards, tom lane



В списке pgsql-performance по дате сообщения:

От: Huan Ruan
Дата:
Сообщение: Re: hash join vs nested loop join
От: Jeff Janes
Дата:
Сообщение: Re: Performance on Bulk Insert to Partitioned Table