Re: Optimizer questions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
Subject: Re: Optimizer questions
Date: 2016-03-09 22:56:52
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> writes:
> I think that the best approach is to generate two different paths:
> original one, when projection is always done before sort and another one
> with postponed projection of non-trivial columns. Then we compare costs
> of two paths and choose the best one.
> Unfortunately, I do not understand now how to implement it with existed
> grouping_planner.
> Do you think that it is possible?

After fooling with this awhile, I don't think it's actually necessary
to do that. See attached proof-of-concept patch.

Although this patch gets through our regression tests, that's only because
it's conservative about deciding to postpone evaluation; if it tried to
postpone evaluation in a query with window functions, it would fail
miserably, because pull_var_clause doesn't know about window functions.
I think that that's a clear oversight and we should extend it to offer
the same sorts of behaviors as it does for Aggrefs. But that would be
slightly invasive, there being a dozen or so callers; so I didn't bother
to do it yet pending comments on this patch.

I think it's probably also broken for SRFs in the tlist; we need to work
out what semantics we want for those. If we postpone any SRF to after
the Sort, we can no longer assume that a query LIMIT enables use of
bounded sort (because the SRF might repeatedly return zero rows).
I don't have a huge problem with that, but I think now would be a good
time to nail down some semantics.

Some other work that would be useful would be to refactor so that the
cost_qual_eval operations aren't so redundant. But that's just a small
time savings not a question of functionality.

And we'd have to document that this changes the behavior for volatile
functions. For the better, I think: this will mean that you get
consistent results no matter whether the query is implemented by
indexscan or seqscan-and-sort, which has never been true before.
But it is a change.

Do people approve of this sort of change in general, or this patch
approach in particular? Want to bikeshed the specific
when-to-postpone-eval policies implemented here? Other comments?

regards, tom lane

Attachment Content-Type Size
conditionally-postpone-tlist-eval-to-after-sort-1.patch text/x-diff 13.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2016-03-09 22:57:56 Re: TAP / recovery-test fs-level backups, psql enhancements etc
Previous Message Breen Hagan 2016-03-09 22:44:18 Re: BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled