Re: Speeding up query pulling comments from pg_catalog

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, Jul 20, 2019 at 7:46 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
But if you do so manually you'll end up with something like

SELECT c.relname AS table, a.attname AS column, d.description AS comment
FROM
  pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
  LEFT JOIN pg_catalog.pg_description d ON d.classoid = c.tableoid and d.objoid = c.oid and d.objsubid = a.attnum
WHERE d.description IS NOT NULL;

For me, that formulation is quite a bit faster than the original ---

A lot faster for me too (~30-40 ms).  Thanks!
 

and then to add insult to injury, has to search pg_description a second
time for each hit.
 
Not sure if I'm understanding this correctly, but are you saying that because col_description() is specified in two places in the query, that it actually will get called twice?  I was under the impression that a function (at least a non-volatile one) specified multiple times, but with the same arguments, would only get called once. Is that just wishful thinking?

Cheers,

Ken


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux