Re: [HACKERS] path toward faster partition pruning

On 7 March 2018 at 10:15, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Mar 2, 2018 at 7:32 PM, David Rowley
> <david.rowley@2ndquadrant.com> wrote:
>> It appears to me, for your method to work we'd need to try every
>> combination of the clauses matching each partition key, which in this
>> case is 3 * 3 * 3 searches. Amit's current method is 1 search, after
>> the clause reduction which is 3 + 3 + 3 (O(N) per partition key)
> [...]
>> With that considered, is it still a good idea to do it this way?
>
> I dunno.  What do you think?
>
> That case is indeed pretty unfortunate, but it's also pretty
> artificial.  It's not obvious to me that we shouldn't care about it,
> but it's also not obvious to me that we should.  If we have some
> bizarre cases that slip through the cracks or don't perform terribly
> well, maybe nobody would ever notice or care.  On the other hand,
> maybe they would.

One thing I've learned in my time working with PostgreSQL is that, if
there's a known hole, someone's probably going to fall down it
eventually.  I like working with PostgreSQL because we're pretty
careful to not make holes that people can fall down, or if there is
some hole that cannot be filled in, we try to put a fence around it
with a sign, (e.g rename pg_xlog to pg_wal).  I'm not strongly opposed
to your ideas, I probably don't have a complete understanding of the
idea anyway. But from what I understand it looks like you want to take
something that works quite well and make it work less well, and there
appears not to be a good reason provided of why you want to do that.

Is it because you want to simplify the patch due to concerns about it
being too much logic to get right for PG11?

> One thing that we could do is just only accept one clause for each
> column-strategy pairing, presumably either the first one or the last
> one.

The problem with that is it can cause surprising behaviour. We reorder
clauses and clauses get pushed down from upper parts of the query.

Let's say there was some view like:

CREATE VIEW vw_ledger_2018 AS SELECT * FROM ledger WHERE postdate
BETWEEN '2018-01-01' AND '2018-12-13';

And a user comes along and does:

SELECT * FROM vw_ledger_2018 WHERE postdate BETWEEN '2018-03-01' AND
'2018-03-31'

We're going to end up with base quals something like: postdate >=
'2018-01-01' AND postdate <= '2018-12-31' AND postdate >= '2018-03-01'
AND postdate <= '2018-03-31'

If we just take the first from each op strategy then we'll not have
managed to narrow the case down to just the March partition. You might
argue that this should be resolved at some higher level in the
planner, but that does nothing for the run-time pruning case.

I don't really want to do or say anything that jeopardises this patch
from getting into PG11, so if the path of least resistance is to go
with the option you've proposed then I'd much rather that than this
getting pushed out to PG12.  I really just want to try to make sure
we've thought of everything before we create too many surprises for
users.

Perhaps a compromise would be to check all quals from the first
partition key and only the first or last one from the remaining keys.
I imagine most cases will have just 1 key anyway. This would
significantly reduce the number of possible combinations of quals to
try, but unfortunately, it still does have that element of surprise.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services