Re: slow queries over information schema.tables

Hi,

On 2018-12-05 13:22:23 -0500, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2018-12-05 12:24:54 -0500, Tom Lane wrote:
> >> There are two different issues in that. One is that the domain might
> >> have constraints (though in reality it does not), so the planner can't
> >> throw away the CoerceToDomain node, and thus can't match the expression
> >> to the index. Even if we did throw away the CoerceToDomain, it still
> >> would not work because the domain is declared to be over varchar, and
> >> so there's a cast-to-varchar underneath the CoerceToDomain.
>
> > Couldn't we make expression simplification replace CoerceToDomain with a
> > RelabelType if the constraint is simple enough? That's not entirely
> > trivial because we'd have to look into the typecache to get the
> > constraints, but that doesn't sound too bad.
>
> Not following what you have in mind here? My 0002 throws away the
> CoerceToDomain if there are *no* constraints, but I can't see any
> situation in which we'd likely be able to ignore a constraint,
> simple or not.

Yea, simple probably means nonexistant for now. We could e.g. optimize
some NOT NULL checks away, but it's probably not worth it.

> >> 0003 essentially converts "namecol::text texteq textvalue" into
> >> "namecol nameeqtext textvalue", relying on the new equality
> >> operator introduced by 0001.
>
> > Ugh, that's indeed a bit kludgy. It'd be nice to have an approach that's
> > usable outside of one odd builtin type. I was wondering for a bit
> > whether we could have logic to move the cast to the other side of an
> > operator, but I don't see how we could make that generally safe.
>
> Yeah. It seems like it could be a special case of a more general
> expression transform facility, but we have no such facility now.
>
> On the other hand, all of match_special_index_operator is an ugly
> single-purpose kluge already, so I'm not feeling that awful about
> throwing another special case into it. Someday it would be nice
> to replace that code with something more general and extensible,
> but today is not that day as far as I'm concerned.

Fair enough.

Greetings,

Andres Freund