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

Re: Weird index or sort behaviour

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Matthew Wakeling <matthew(at)flymine(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Weird index or sort behaviour
Date: 2009-08-18 17:44:21
Message-ID: 407d949e0908181044g3557ab5bw93886a06a6b374b4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Aug 18, 2009 at 5:57 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Hmmm ... actually, after looking at the code, I notice that we only
> consider adding a Materialize node to buffer an inner input that is a
> Sort node.  The idea was suggested by Greg Stark, if memory serves.
> I wonder now if it'd be worthwhile to generalize that to consider
> adding a Materialize above *any* inner mergejoin input.

If my recollection is right the reason we put the materialize above
the sort node has to do with Simon's deferred final merge pass
optimization. The materialize was a way to lazily build the final
merge as we do the merge but still have the ability to rewind.

I would be more curious in the poster's situation to turn off
enable_seqscan, enable_sort, and/or enable_nestloop see how the index
scan merge join plan runs. rewinding an index scan is more expensive
than rewinding a materialize node but would it really be so much
expensive that it's worth copying the entire table into temporary
space?

-- 
greg
http://mit.edu/~gsstark/resume.pdf

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-08-18 17:57:11
Subject: Re: Weird index or sort behaviour
Previous:From: Tom LaneDate: 2009-08-18 16:57:29
Subject: Re: Weird index or sort behaviour

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