Re: slow queries over information schema.tables

On Wed, Dec 5, 2018 at 1:41 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ah, yes, that is a case where we might have enough knowledge to prove
> the test redundant --- but considering that we explicitly discourage
> domain NOT NULL as bad style and not fully supported, I can't get
> excited about it. I suppose in some cases we might be able to use
> predtest.c to prove domain CHECK constraints redundant, but I bet that
> it's not worth the trouble.
>
> The bigger picture here is that people seem to like to use domains
> as simple type aliases, which will never have any constraints, but
> we're very dumb about that today. So the patch as presented seems
> like it has lots of potential applicability, as long as we fix the
> invalidation aspect.

I'm not thrilled about depending on sinval without locking,
particularly given that my proposal to make sure we
AcceptInvalidationMessages() at least once per query was shouted down.
That means that in corner cases, you could execute many queries
without flushing the old cache entries. However, I do agree that
locking every type, function, operator, etc. involved in the query is
impractical.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company