Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT) - Mailing list pgsql-interfaces

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Douglas Thomson <dougt@mugc.cc.monash.edu.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
:varattno15 :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
:varlevelsup0 :varnoold 2 :varoattno 6} { VAR :varno 65001 :varattno 2 :vartype 25 :vartypmod -1  :varlevelsup 0
:varnoold1 :varoattno 2})} { EXPR :typeOid 0  :opType op :oper { OPER :opno 98 :opid 67 :opresulttype 16 } :args ({ VAR
:varno65000 :varattno 3 :vartype 25 :vart! 
ypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 4} { VAR :varno 65001 :varattno 1 :vartype 25 :vartypmod -1
:varlevelsup0 :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
:resjunkfalse } :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
:resjunkfalse } :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
:cost3.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
:vartype25 :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
:vartype25 :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
:vartype25 :vartypmod -1  :varlevelsup 0 :var! 
noold 0 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 25 :restypmod -1 :resname "<>" :reskey 0
:reskeyop0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 4 :vartype 25 :vartypmod -1  :varlevelsup 0
:varnoold0 :varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 25 :restypmod -1 :resname "<>" :reskey 0
:reskeyop0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 5 :vartype 25 :vartypmod -1  :varlevelsup 0
:varnoold0 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 25 :restypmod -1 :resname "<>" :reskey 0
:reskeyop0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 6 :vartype 25 :vartypmod -1  :varlevelsup 0
:varnoold0 :varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 25 :restypmod -1 :resname "<>" :reskey 0
:reskeyop0 :resgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 7 :vartype 25 :vartypmod -1  :varlevelsup 0
:varnoold0 :varoattno 7}} { TARGETENTRY :resdom { ! 
RESDOM :resno 8 :restype 25 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false }NOTICE:
QUERYPLAN: 

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=14315width=72) 
                                                                    ->  Hash  (cost=302.51 rows=6955 width=24)
                                                                          ->  Seq Scan on t24 users  (cost=302.51
rows=6955width=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.