Re: view reading information_schema is slow in PostgreSQL 12
On Sat, 13 Jun 2020 at 16:07, Tom Lane wrote: > > David Rowley writes: > > I wondered if it would be more simple to add some smarts to look a bit > > deeper into case statements for selectivity estimation purposes. An > > OpExpr like: > > CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN > > 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK'; > > Hm. Maybe we could reasonably assume that the equality operators used > for such constructs are error-and-side-effect-free, thus dodging the > semantic problem I mentioned in the other thread? I'm only really talking about selectivity estimation only for now. I'm not really sure why we'd need to ensure that the equality operator is error and side effect free. We'd surely only be executing the case statement's operator's oprrest function? We'd need to ensure we don't invoke any casts that could error out. David
Re: view reading information_schema is slow in PostgreSQL 12
On Sat, 13 Jun 2020 at 19:52, David Rowley wrote: > > On Sat, 13 Jun 2020 at 16:07, Tom Lane wrote: > > > > David Rowley writes: > > > I wondered if it would be more simple to add some smarts to look a bit > > > deeper into case statements for selectivity estimation purposes. An > > > OpExpr like: > > > CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN > > > 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK'; > > > > Hm. Maybe we could reasonably assume that the equality operators used > > for such constructs are error-and-side-effect-free, thus dodging the > > semantic problem I mentioned in the other thread? > > I'm only really talking about selectivity estimation only for now. > I'm not really sure why we'd need to ensure that the equality operator > is error and side effect free. We'd surely only be executing the case > statement's operator's oprrest function? We'd need to ensure we don't > invoke any casts that could error out. Hmm, after a bit of thought I now see what you mean. We'd need to loop through each WHEN clause to ensure there's a Const and check if that Const is equal to the Const on the other side of the OpExpr, then select the first match. That, of course, must perform a comparison, but, that's not really doing anything additional to what constant folding code already does, is it? David
Re: view reading information_schema is slow in PostgreSQL 12
David Rowley writes: > On Sat, 13 Jun 2020 at 19:52, David Rowley wrote: >> On Sat, 13 Jun 2020 at 16:07, Tom Lane wrote: >>> Hm. Maybe we could reasonably assume that the equality operators used >>> for such constructs are error-and-side-effect-free, thus dodging the >>> semantic problem I mentioned in the other thread? >> I'm only really talking about selectivity estimation only for now. >> I'm not really sure why we'd need to ensure that the equality operator >> is error and side effect free. We'd surely only be executing the case >> statement's operator's oprrest function? We'd need to ensure we don't >> invoke any casts that could error out. > Hmm, after a bit of thought I now see what you mean. No, you were right the first time: we're considering different things. I was wondering about how to constant-fold a "CASE = constant" construct as was being requested in the other thread. Obviously, if that succeeds then it'll simplify selectivity estimation too --- but it's reasonable to also think about what to do for "CASE = constant" in selectivity estimation, because with or without such a constant-folding rule, there would be lots of cases that the rule fails to simplify. Further we should be thinking about how to get some estimate for cases that the folding rule would fail at, so I'm not sure that we ought to restrict our thoughts to constant comparisons. In the cases I've seen so far, even a rule as dumb as "if the CASE has N arms then estimate selectivity as 1/N" would be a lot better than what we get now. regards, tom lane
