From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthew Wakeling <matthew@flymine.org>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Corin <wakathane@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: too complex query plan for not exists query and multicolumn indexes
Date: Mon, 22 Mar 2010 08:40:12 -0400
Message-ID: <16237.1269261612@sss.pgh.pa.us> (raw)
In-Reply-To: <alpine.DEB.2.00.1003221100080.9798@aragorn.flymine.org>
References: <4BA36D7B.6010506@gmail.com>
<20100319182750.GQ21875@tamriel.snowman.net>
<alpine.DEB.2.00.1003221100080.9798@aragorn.flymine.org>
Matthew Wakeling <matthew@flymine.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, matthew@flymine.org, sfrost@snowman.net, wakathane@gmail.com
Subject: Re: too complex query plan for not exists query and multicolumn indexes
In-Reply-To: <16237.1269261612@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox