Search Postgresql Archives

Directly programmed query plans?

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

 



I vaguely recall that there’s an interface to PostgreSQL 7.3 where developers can directly feed it query plans. Can someone help me remember where this lived and whether it worked?

 

The problem I’m looking at is that it’s difficult to impossible to get optimal uses of indexes all the time. Some of our queries are “optimized” particularly poorly. Perhaps, just perhaps it might be useful to be able to write a query using the compiled form rather than the SQL form. Any suggestions to help solve the real problem of index hinting would also be welcomed.

 

Example SQL:

SELECT n.nspname as "Schema",

              c.relname as "Name",

              CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",

              u.usename as "Owner"

            FROM pg_catalog.pg_class c

                 LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner

                 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

            WHERE c.relkind IN ('r','v','S','')

                  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')

                  AND pg_catalog.pg_table_is_visible(c.oid)

            ORDER BY 1,2;

 

 

Example plan (goes on for many more pages):

DETAIL:  {SORT :startup_cost 11.68 :total_cost 11.74 :plan_rows 25 :plan_width 193

            :targetlist ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 19 :restypmod -1

            :resname Schema :ressortgroupref 1 :resorigtbl 16595 :resorigcol 1 :resjunk

            false} :expr {VAR :varno 4 :varattno 1 :vartype 19 :vartypmod -1 :varlevelsup

            0 :varnoold 4 :varoattno 1}} {TARGETENTRY :resdom {RESDOM :resno 2 :restype 19

            :restypmod -1 :resname Name :ressortgroupref 2 :resorigtbl 1259 :resorigcol 1

            :resjunk false} :expr {VAR :varno 1 :varattno 1 :vartype 19 :vartypmod -1

            :varlevelsup 0 :varnoold 1 :varoattno 1}} {TARGETENTRY :resdom {RESDOM :resno

            3 :restype 25 :restypmod -1 :resname Type :ressortgroupref 0 :resorigtbl 0

            :resorigcol 0 :resjunk false} :expr {CASE :casetype 25 :arg <> :args ({WHEN

            :expr {OPEXPR :opno 92 :opfuncid 61 :opresulttype 16 :opretset false :args

            ({VAR :varno 1 :varattno 13 :vartype 18 :vartypmod -1 :varlevelsup 0 :varnoold

            1 :varoattno 13} {CONST :consttype 18 :constlen 1 :constbyval true

 

Joshua ben Jore

Sr Software Engineer 2

 

W H I T E P A G E S .C O M  |  I N C

p: 206.812.9211
jbenjore@xxxxxxxxxxxxxx

 

www.whitepagesinc.com

 

The information contained in this message may be privileged, confidential, and protected from disclosure. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux