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

Re: Weird index or sort behaviour

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Greg Stark <gsstark(at)mit(dot)edu>
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:57:11
Message-ID: 26185.1250618231@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Greg Stark <gsstark(at)mit(dot)edu> writes:
> 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?

Absolutely not, but remember that what we're expecting the Materialize
to do is buffer only as far back as the last Mark, so that it's unlikely
ever to spill to disk.  It might well be a win to do that rather than
re-fetching from the indexscan.  The incremental win compared to not
having the materialize would be small compared to what it is for a sort,
but it could still be worthwhile I think.  In particular, in Matthew's
example the sort is being estimated at significantly higher cost than
the indexscan, which presumably means that we are estimating there will
be a *lot* of re-fetches, else we wouldn't have rejected the indexscan
on the inside.  Inserting a materialize would make the re-fetches
cheaper.  I'm fairly sure that this plan structure would cost out
cheaper than the sort according to cost_mergejoin's cost model.  As
noted in the comments therein, that cost model is a bit oversimplified,
so it might not be cheaper in reality ... but we ought to try it.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-08-18 18:40:18
Subject: Re: Weird index or sort behaviour
Previous:From: Greg StarkDate: 2009-08-18 17:44:21
Subject: Re: Weird index or sort behaviour

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