Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>, Corin <wakathane(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: too complex query plan for not exists query and multicolumn indexes
Date: 2010-03-22 12:40:12
Message-ID: 16237.1269261612@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Matthew Wakeling <matthew(at)flymine(dot)org> 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

In response to

pgsql-performance by date

Next:From: Matthew WakelingDate: 2010-03-22 13:29:49
Subject: Re: GiST index performance
Previous:From: Matthew WakelingDate: 2010-03-22 11:48:44
Subject: Re: too complex query plan for not exists query and multicolumn indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group