|
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 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. |
