On 7 March 2018 at 10:15, Robert Haas <robertmh...@gmail.com> wrote: > On Fri, Mar 2, 2018 at 7:32 PM, David Rowley > <david.row...@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