From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: regrog <andrea.vencato@gmail.com>,
        pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: view reading information_schema is slow in PostgreSQL 12
In-reply-to: 
 <CAApHDvpeZ1q79YZwn7qnCvaRFPMkTixPnuaBgn3JF=1+skd9Yg@mail.gmail.com>
References: <1591975278775-0.post@n3.nabble.com>
 <798686.1592017869@sss.pgh.pa.us>
 <CAApHDvrnop0h+du73FZKEzxd0CGP3RUoWPNha2ekz_F0YMNaYA@mail.gmail.com>
 <800752.1592021253@sss.pgh.pa.us>
 <CAApHDvrj9b3OrDt_Yc-21LUF23_hd=Ni5o856xXGidaQtQ__GQ@mail.gmail.com>
 <CAApHDvpeZ1q79YZwn7qnCvaRFPMkTixPnuaBgn3JF=1+skd9Yg@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Sat, 13 Jun 2020 20:22:09 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <845803.1592068006.1@sss.pgh.pa.us>
Date: Sat, 13 Jun 2020 13:06:46 -0400
Message-ID: <845804.1592068006@sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Sat, 13 Jun 2020 at 19:52, David Rowley <dgrowleyml@gmail.com> wrote:
>> On Sat, 13 Jun 2020 at 16:07, Tom Lane <tgl@sss.pgh.pa.us> 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