Re: domain type smashing is expensive

On Tue, Sep 12, 2017 at 1:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On short-running queries that return a lot of columns,
>> SendRowDescriptionMessage's calls to getBaseTypeAndTypmod() are a
>> noticeable expense.
>
> Yeah, I was never very happy with the way that the original domain
> patch dealt with that. I think you're not even focusing on the
> worst part, which is all the getBaseType calls in the parser.
> I do not have a good idea about how to get rid of them though.

Well, I'm focusing on the part that shows up in the profile. Prepared
queries don't get re-parsed repeatedly, so the calls in the parser
don't matter in that context. I'm not saying it wouldn't be nice to
get rid of them, but it only helps people who aren't preparing their
queries.

>> + if (typid < FirstBootstrapObjectId)
>> + break;
>
> I'm really unwilling to buy into an assumption that we'll never
> have any built-in domains just to support such a crock as this.

I more or less expected that reaction, but I think it's a bit
short-sighted. If somebody wanted to define a domain type in
pg_type.h, they'd have to write any domain constraint out in
pg_constraint.h in nodeToString() form, and it seems to me that the
chances that we'd accept a patch are pretty much nil, because it would
be a maintenance nuisance. Now, maybe you could argue that somebody
might want to define a constraint-less domain in pg_type.h, but I
can't recall any proposal to do such a thing and don't see why
anybody'd want to do it.

> You'd need to dig around in the archives from around that time. But
> my hazy recollection is that the argument was that clients would be
> much more likely to know what to do with a built-in type than with
> some domain over it. psql, for example, knows about right-justifying
> the built-in numeric types, but it'd fail to do so for domains.

Mmm, that's a good point.

>> 2. Precompute the list of types to be sent to the client during
>> planning instead of during execution. The point of prepared
>> statements is supposed to be to do as much of the work as possible at
>> prepare time so that bind/execute is as fast as possible, but we're
>> not really adhering to that design philosophy here. However, I don't
>> have a clear idea of exactly how to do that.
>
> That'd help for prepared statements, but not for simple query execution.

Sure, but that's kinda my point. We've got to send a RowDescription
message for every query, and if that requires smashing domain types to
base types, we have to do it. What we don't have to do is repeat that
work for every execution of a prepared query.

> The trick here is that I don't think we want to change the returned column
> types for queries that are not being sent to a client. The parser and
> planner aren't really aware of that context ATM. Maybe we could make them
> so?

I guess it depends on whether that context is mutable. Can I Parse a
query to create a prepared statement, then use that from a stored
procedure? If so, then it's not firmly known at plan time what the
execution context will be.

> But it still seems like a kluge that is only addressing a small part
> of the domain-smashing issue.
>
> I wonder if it'd help to put some kind of bespoke cache into getBaseType.
> We've done that elsewhere, eg operator lookup.

That might be a possibility, although I feel like it's likely to be
substantially less effective than the quick hack, and it's not really
attacking the problem at the root anyway.

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