Re: Scrollable cursors and Sort performance

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Scrollable cursors and Sort performance
Date: 2006-02-27 14:17:23
Message-ID: 1141049843.2871.163.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

On Sun, 2006-02-26 at 19:26 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > On Fri, 2006-02-10 at 11:58 -0500, Tom Lane wrote:
> >> I suspect that the right thing is not to see this as a planner issue at
> >> all, but to try to drive the choice off the context in which the plan
> >> gets invoked. Possibly we could pass a "need random access" boolean
> >> down through the ExecInitNode calls (I seem to recall some prior
> >> discussion of doing something like that, in the context of telling
> >> Materialize that it could be a no-op in some cases).
>
> > Yeh, that was me just being a little vague on implementation, but
> > handing off from planner to executor via the Plan node is what I was
> > hacking at now. I'll follow your recommendation and do it for the
> > general case. Propagating it down should allow a few similar
> > optimizations.
>
> Have you done anything with this idea yet? I was just thinking of
> attacking it myself.

Started, but have been side-tracked by other urgent matters.

Happy to complete this today with you? This mini-project has made me
realise I don't understand the executor as well as I should, so I'm keen
to see it through, even if I'm a bit slower at it.

> After looking at my old notes about Materialize,
> I am thinking that we should add a "int flags" parameter to the InitNode
> calls along with ExecutorStart and probably PortalStart. This would
> contain a bitwise OR of at least the following flag bits:
>
> need-ReScan
> need-backwards-scan
> need-mark-restore
> no-execute (so flags can replace ExecutorStart's explainOnly param)
>
> We'd have lots of room for expansion, but these are the ones that seem
> to have immediate use. And most callers of ExecutorStart/PortalStart
> know they don't need these things, so could just pass zero always.

Design-wise I was looking at putting a named struc in there, so it would
be easily expandible in the future to carry anything else that needs to
be passed down through the nodes like this. I guess thats the same
line-of-thought you're on too.

> Interesting point: how should EXPLAIN ANALYZE set these bits? For its
> own purposes it need not request random access, but it might be
> interesting to make it possible to examine both the random and nonrandom
> behaviors, now that these will be significantly different performancewise.
> Possibly we could make EXPLAIN ANALYZE EXECUTE set the random-access
> bits.

Good point. Whichever we do will be wrong in some cases.... I've no real
opinion on this other than a vague preference for it to be quick.

Best Regards, Simon Riggs

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Woodward 2006-02-27 14:39:59 Re: pg_config, pg_service.conf, postgresql.conf ....
Previous Message Javier Somoza 2006-02-27 12:29:19 Re: wal sync method

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2006-02-27 15:07:24 Re: Scrollable cursors and Sort performance
Previous Message Peter Eisentraut 2006-02-27 12:55:04 Re: Uninstall scripts for contrib