From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> |
Cc: | Postgres Hackers List <hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] Almost there on column aliases |
Date: | 2000-02-11 07:20:38 |
Message-ID: | 18803.950253638@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)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.b
from rtest_t2, rtest_t3
where 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 :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 :varnoold 2 :varoattno 2}}) :qual { EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :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
:varnoold 2
:varoattno 2
}
}
)
:qual
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 96
:opid 0
:opresulttype 16
}
:args (
{ VAR
:varno 1
: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 :restype 23 :restypmod -1 :resname a :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000 :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 65001 :varattno 1 :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 :varoattno 1}}) :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 :varattno 1 :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 :opresulttype 16 } :args ({ VAR :varno 65001 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1} { 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(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Inoue | 2000-02-11 07:35:02 | RE: [HACKERS] how to make libpq on winnt using the 'win32.mak's |
Previous Message | Thomas Lockhart | 2000-02-11 07:16:21 | Re: [HACKERS] Almost there on column aliases |