Re: too complex query plan for not exists query and multicolumn indexes

От: Tom Lane
Тема: Re: too complex query plan for not exists query and multicolumn indexes
Дата: ,
Msg-id: 16237.1269261612@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: too complex query plan for not exists query and multicolumn indexes  (Matthew Wakeling)
Список: pgsql-performance


Matthew Wakeling <> writes:
> On Fri, 19 Mar 2010, Stephen Frost wrote:
>> ...it has to go to an external on-disk sort (see later on, and how to
>> fix that).

> This was covered on this list a few months ago, in
> http://archives.postgresql.org/pgsql-performance/2009-08/msg00184.php and
> http://archives.postgresql.org/pgsql-performance/2009-08/msg00189.php

> There seemed to be some consensus that allowing a materialise in front of
> an index scan might have been a good change. Was there any movement on
> this front?

Yes, 9.0 will consider plans like

 Merge Join  (cost=0.00..14328.70 rows=1000000 width=488)
   Merge Cond: (a.four = b.hundred)
   ->  Index Scan using fouri on tenk1 a  (cost=0.00..1635.62 rows=10000 width=244)
   ->  Materialize  (cost=0.00..1727.16 rows=10000 width=244)
         ->  Index Scan using tenk1_hundred on tenk1 b  (cost=0.00..1702.16 rows
=10000 width=244)

Some experimentation shows that it won't insert the materialize unless
quite a bit of re-fetching is predicted (ie neither side of the join is
unique).  We might need to tweak the cost parameters once we get some
field experience with it.

            regards, tom lane


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

От: Justin Graf
Дата:
Сообщение: Re: too complex query plan for not exists query and multicolumn indexes
От: Scott Carey
Дата:
Сообщение: Re: Block at a time ...