Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Douglas Thomson <dougt(at)mugc(dot)cc(dot)monash(dot)edu(dot)au> writes:
> > I have just noticed that the back end process swells to
> > the total size of all the tables involved in my query for the
> > duration of the SELECT (or is it the duration of the declared cursor
> > now?).
>
> The life of the cursor, probably, but I'm a little surprised by this.
>
> > To summarise the situation:
> > - I am attempting a join on 9 tables, using a cursor to fetch the
> > selected data
> > - the back end process grows to over 20M while processing my query
>
> What query plan does EXPLAIN show for your SELECT?

OK, here goes! I don't profess to understand the output from EXPLAIN,
so here it is verbatim. If I hadn't checked that I have indexes on
all the attributes (and attribute combinations) that make up the keys
needed for the join, I would think the back end was trying to read in
and sort a large part of the data just for the query!

NOTICE: _outNode: don't know how to print type 21
NOTICE: QUERY DUMP:

{ NESTLOOP :cost 0 :size 600832 :width 240 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 25 :restypmod -1 :resname "name" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno 11 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 9 :varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname "canonical" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno 15 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 5 :varoattno 2}}) :qpqual ({ EXPR :typeOid 0 :opType op :oper { OPER :opno 98 :opid 67 :opresulttype 16 } :args ({ VAR :varno 65000 :varattno 4 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 6} { VAR :varno 65001 :varattno 2 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2})} { EXPR :typeOid 0 :opType op :oper { OPER :opno 98 :opid 67 :opresulttype 16 } :args ({ VAR :varno 65000 :varattno 3 :vartype 25 :vart!
ypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 4} { VAR :varno 65001 :varattno 1 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})}) :lefttree { SEQSCAN :cost 0 :size 0 :width 24 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2}}) :qpqual ({ EXPR :typeOid 0 :opType op :oper { OPER :opno 98 :opid 67 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 3 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} { CONST :consttype 25 :constlen -1 :constisnull false :constvalue 32 [ 32 0 0 0 47 83 117 98 !
106 101 99 116 115 47 49 57 57 57 47 115 101 109 49 47 71 67 79 47 49 56 49 50 ] :constbyval false })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 } :righttree { SEQSCAN :cost 3.64259e+07 :size 1073741849 :width 216 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 1 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 0 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 2 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 0 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 3 :vartype 25 :vartypmod -1 :varlevelsup 0 :var!
noold 0 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 4 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 0 :varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 5 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 0 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 6 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 0 :varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 7 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 0 :varoattno 7}} { TARGETENTRY :resdom { !
RESDOM :resno 8 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false }NOTICE: QUERY PLAN:

Nested Loop (cost=0.00 rows=600832 width=240)
-> Seq Scan on t25 agents (cost=0.00 rows=0 width=24)
-> Seq Scan (cost=36425892.00 rows=1073741849 width=216)
-> ??? (cost=36425892.00 rows=1073741849 width=216)
-> Merge Join (cost=36425892.00 rows=1073741849 width=216)
-> Merge Join (cost=8371644.00 rows=850088153 width=192)
-> Seq Scan (cost=8110708.50 rows=7333003 width=144)
-> Sort (cost=8110708.50 rows=7333003 width=144)
-> Hash Join (cost=13897.26 rows=7333003 width=144)
-> Hash Join (cost=4635.32 rows=280070 width=132)
-> Hash Join (cost=2674.97 rows=59324 width=120)
-> Hash Join (cost=2180.41 rows=14894 width=108)
-> Hash Join (cost=1705.82 rows=14315 width=96)
-> Seq Scan on t30 enrolments (cost=667.40 rows=14315 width=72)
-> Hash (cost=302.51 rows=6955 width=24)
-> Seq Scan on t24 users (cost=302.51 rows=6955 width=24)
-> Hash (cost=1.10 rows=3 width=12)
-> Seq Scan on t21 studymodes (cost=1.10 rows=3 width=12)
-> Hash (cost=1.53 rows=16 width=12)
-> Seq Scan on t29 periods (cost=1.53 rows=16 width=12)
-> Hash (cost=1.33 rows=10 width=12)
-> Seq Scan on t23 campuses (cost=1.33 rows=10 width=12)
-> Hash (cost=10.32 rows=252 width=12)
-> Seq Scan on t28 courses (cost=10.32 rows=252 width=12)
-> Seq Scan (cost=17962.40 rows=29831 width=48)
-> Sort (cost=17962.40 rows=29831 width=48)
-> Seq Scan on t27 offerings (cost=1457.42 rows=29831 width=48)
-> Index Scan using t26_unq_0 on t26 subjects (cost=894.95 rows=13439 width=24)

NOTICE: _outNode: don't know how to print type 21

Hope this means something to you,

Doug.