Received: from mugca.cc.monash.edu.au (dougt@mugca.cc.monash.edu.au [130.194.208.7]) by hub.org (8.9.3/8.9.3) with ESMTP id VAA08215 for ; Thu, 2 Dec 1999 21:12:57 -0500 (EST) (envelope-from dougt@mugc.cc.monash.edu.au) Received: (from dougt@localhost) by mugca.cc.monash.edu.au (8.9.3/8.9.3) id NAA16861; Fri, 3 Dec 1999 13:12:48 +1100 (EST) Message-Id: <199912030212.NAA16861@mugca.cc.monash.edu.au> Subject: Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT) To: tgl@sss.pgh.pa.us (Tom Lane) Date: Fri, 3 Dec 1999 13:12:48 +1100 (EST) Cc: dougt@mugc.cc.monash.edu.au, pgsql-interfaces@postgreSQL.org In-Reply-To: <29675.944181689@sss.pgh.pa.us> from "Tom Lane" at Dec 2, 99 07:41:29 pm Reply-To: dougt@mugc.cc.monash.edu.au From: Douglas Thomson X-Mailer: ELM [version 2.4 PL25] Content-Type: text Tom Lane writes: > Douglas Thomson 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.