Re: [HACKERS] Almost there on column aliases

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> This doesn't look very detailed, is it really what you wanted?

> Hmm. I expected to get a full plan (labeled "plan:"). Did you do the
> query or just an "explain"?

I'm sorry, just did the "explain".  Is this any better?

StartTransactionCommand
query: select rtest_t2.a, rtest_t3.bfrom rtest_t2, rtest_t3where rtest_t2.a = rtest_t3.a
parser outputs:

{ QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :unionall
false:distinctClause <> :sortClause <> :rtable ({ RTE :relname rtest_t2 :refname rtest_t2 :relid 404330 :inh false
:inFromCltrue :inJoinSet true :skipAcl false} { RTE :relname rtest_t3 :refname rtest_t3 :relid 404340 :inh false
:inFromCltrue :inJoinSet true :skipAcl false}) :targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod-1 :resname a :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1
:vartype23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23
:restypmod-1 :resname b :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 2
:vartype23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 2}}) :qual { EXPR :typeOid 16  :opType op :oper { OPER
:opno96 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 :v!
 
!
arattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} { VAR :varno 2 :varattno 1 :vartype 23
:vartypmod-1  :varlevelsup 0 :varnoold 2 :varoattno 1})} :groupClause <> :havingQual <> :hasAggs false :hasSubLinks
false:unionClause <> :intersectClause <> :limitOffset <> :limitCount <> :rowMark <>}
 

after rewriting:
{ QUERY   :command 1    :utility <>   :resultRelation 0   :into <>   :isPortal false   :isBinary false   :isTemp false
:unionall false   :distinctClause <>   :sortClause <>   :rtable (     { RTE      :relname rtest_t2      :refname
rtest_t2     :relid 404330      :inh false      :inFromCl true      :inJoinSet true      :skipAcl false     }        {
RTE     :relname rtest_t3      :refname rtest_t3      :relid 404340      :inh false      :inFromCl true      :inJoinSet
true     :skipAcl false     }  )     :targetlist (     { TARGETENTRY      :resdom         { RESDOM         :resno 1
   :restype 23         :restypmod -1         :resname a         :reskey 0         :reskeyop 0         :ressortgroupref
0        :resjunk false         }           :expr         { VAR         :varno 1         :varattno 1         :vartype
23        :vartypmod -1          :varlevelsup 0         :varnoold 1         :varoattno 1        }     }        {
TARGETENTRY     :resdom         { RESDOM         :resno 2         :restype 23         :restypmod -1         :resname b
      :reskey 0         :reskeyop 0         :ressortgroupref 0         :resjunk false         }           :expr
{VAR         :varno 2         :varattno 2         :vartype 23         :vartypmod -1          :varlevelsup 0
:varnoold2         :varoattno 2        }     }  )     :qual      { EXPR      :typeOid 16       :opType op      :oper
    { OPER         :opno 96         :opid 0         :opresulttype 16         }           :args (        { VAR
:varno1         :varattno 1         :vartype 23         :vartypmod -1          :varlevelsup 0         :varnoold 1
 :varoattno 1        }              { VAR         :varno 2         :varattno 1         :vartype 23         :vartypmod
-1         :varlevelsup 0         :varnoold 2         :varoattno 1        }     )     }     :groupClause <>
:havingQual<>   :hasAggs false   :hasSubLinks false   :unionClause <>   :intersectClause <>   :limitOffset <>
:limitCount<>   :rowMark <>  }
 

plan:

{ MERGEJOIN :cost 164.658 :rows 10000 :width 12 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1
:restype23 :restypmod -1 :resname a :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000
:varattno1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype23 :restypmod -1 :resname b :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001
:varattno1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 2}}) :qpqual <> :lefttree { SORT :cost
69.8289:rows 1000 :width 8 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 2 :vartype 23
:vartypmod-1  :varlevelsup 0 :varnoold 2 :varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod
-1:resname <> :reskey 1 :reskeyop 66 :ressortg!
 
!
roupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
:varoattno1}}) :qpqual <> :lefttree { SEQSCAN :cost 20 :rows 1000 :width 8 :state <> :qptargetlist ({ TARGETENTRY
:resdom{ RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false
}:expr { VAR :varno 2 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 2}} { TARGETENTRY
:resdom{ RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false
}:expr { VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 1}}) :qpqual <>
:lefttree<> :righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 2 } :righttree <> :extprm () :locprm
():initplan <> :nprm 0  :nonameid 0 :keycount 1 } :righttree { SORT :cost 69.8289 :rows 1000 :width 4 :state <>
:qptargetlist({ TARGETENTRY :resdom { RESDOM :resno 1 :!
 
!
restype 23 :restypmod -1 :resname <> :reskey 1 :reskeyop 66 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree { SEQSCAN :cost
20:rows 1000 :width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod-1  :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm ()
:initplan<> :nprm 0  :scanrelid 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0  :nonameid 0 :keycount 1 }
:extprm() :locprm () :initplan <> :nprm 0  :mergeclauses ({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
:opresulttype16 } :args ({ VAR :varno 65001 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
:varoattno1} { VAR :varno 65000 :varattno 1 :vartype 2!
 
!
3 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1})})}

ProcessQuery
CommitTransactionCommand

>> Any changes in backend/optimizer/ ?  I've got a bunch of uncommitted
>> changes there myself.

> Not too much. Though I've got a null pointer problem in executor for
> mergejoins and I'm not certain where it is coming from.

Could easy be a planner shortcoming.  Maybe you should commit so we
can get more eyeballs on the problem.

> Here are the files which have changed in the optimizer/ tree:

> [postgres@golem optimizer]$ cvs -q update .
> M prep/prepunion.c
> M util/clauses.c

> The changes are minor; I'm pretty sure I can remerge if you want to
> commit your stuff (at least if your stuff is isolated to the
> backend/optimizer/ part of the tree).

I know I've tromped on your toes in the past weeks, so I'll wait for
you to commit and then merge.  I have no changes in those two files,
but I do have some in the usual-suspect places like nodes/copyfuncs.
        regards, tom lane