Directly programmed query plans? - Mailing list pgsql-general

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@whitepages.com

www.whitepagesinc.com