Le 11/12/2014 00:46, Tom Lane a écrit :
> Kevin Grittner <kgrittn@ymail.com> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> As far as that goes, I think the OP was unhappy about the performance
>>> of the information_schema views, which in our implementation do exactly
>>> that so that the exposed types of the view columns conform to the SQL
>>> standard, even though the underlying catalogs use PG-centric types.
>>>
>>> I don't believe that that's the only reason why the performance of the
>>> information_schema views tends to be sucky, but it's certainly a reason.
>
>> Is that schema too "edge case" to justify some functional indexes
>> on the cast values on the underlying catalogs? (I'm inclined to
>> think so, but it seemed like a question worth putting out
>> there....)
>
> We don't support functional indexes on system catalogs, so whether it'd
> be justified is sorta moot. On the whole though I'm inclined to agree
> that the information_schema views aren't used enough to justify adding
> overhead to system-catalog updates, even if the pieces for that all
> existed.
>
>> Or, since these particular domains are known, is there any sane way
>> to "special-case" these to allow the underlying types to work?
>
> I don't particularly care for a kluge solution here.
>
> I notice that recent versions of the SQL spec contain the notion of a
> "distinct type", which is a user-defined type that is representationally
> identical to some base type but has its own name, and comes equipped with
> assignment-grade casts to and from the base type (which in PG terms would
> be binary-compatible casts, though the spec doesn't require that).
> It seems like this might be intended to be the sort of "zero cost type
> alias" we were talking about, except that the SQL committee seems to have
> got it wrong by not specifying the cast-to-base-type as being implicit.
> Which ISTM you would want so that operators/functions on the base type
> would apply automatically to the distinct type. But perhaps we could
> extend the spec with some option to CREATE TYPE to allow the cast to come
> out that way.
>
> Or in short, maybe we should try to replace the domains used in the
> current information_schema with distinct types.
That's interesting and could easily solve the problem.
To give some context, for some reason, Drupal queries the
information_schema views before displaying some pages.
As our customer has many tables (approx 60000 tables, organised "à la
Oracle" with one schema per database user). Thus, the seq scan against
pg_class takes ~50ms and the very same one without the cast takes less
than 1ms.
There is an example of query used :
SELECT column_name, data_type, column_default FROM information_schema.columnsWHERE table_schema = 'one_schema' AND
table_name= 'one_table' AND ( data_type = 'bytea' OR ( numeric_precision IS NOT NULL AND column_default::text
LIKE'%nextval%' ));
Regards
