pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)
5+ messages / 2 participants
[nested] [flat]
* Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)
@ 1999-12-02 23:30 Douglas Thomson <dougt@mugc.cc.monash.edu.au>
0 siblings, 1 reply; 5+ messages in thread
From: Douglas Thomson @ 1999-12-02 23:30 UTC (permalink / raw)
To: dougt@mugc.cc.monash.edu.au; +Cc: tgl@sss.pgh.pa.us; pgsql-interfaces
A few weeks ago I reported a problem I was having with front end
memory consumption in SELECT, and Tom's suggestion about using a
cursor solved that problem very nicely at the front end.
However, my front and back end processes are running on the same
server, and 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?).
To summarise the situation:
- back end processes on my system are usually about 3M
(PostgreSQL 6.5.1, Debian slink, Intel)
- I am attempting a join on 9 tables, using a cursor to fetch the
selected data
- I am only selecting a tiny fraction of the total data
- I am running in serializable mode if it matters
- the files used to store my tables (including associated keys
and indexes etc) take just under 17M on disk
- the back end process grows to over 20M while processing my query
This is a serious concern, because 17M is just some sample data, and
will eventually be much larger.
Any hints for ways of limiting back end memory consumption (hopefully
to something constant rather than something proportional to the
tables sizes)?
Doug.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)
@ 1999-12-03 00:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Douglas Thomson <dougt@mugc.cc.monash.edu.au>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 1999-12-03 00:41 UTC (permalink / raw)
To: dougt@mugc.cc.monash.edu.au; +Cc: pgsql-interfaces
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?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)
@ 1999-12-03 02:12 Douglas Thomson <dougt@mugc.cc.monash.edu.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Douglas Thomson @ 1999-12-03 02:12 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: dougt@mugc.cc.monash.edu.au; 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 :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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)
@ 1999-12-03 03:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Douglas Thomson <dougt@mugc.cc.monash.edu.au>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 1999-12-03 03:10 UTC (permalink / raw)
To: dougt@mugc.cc.monash.edu.au; +Cc: pgsql-interfaces
Douglas Thomson <dougt@mugc.cc.monash.edu.au> writes:
> 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!
Well, of course it *is* ... the trick is to make sure that anything
really large ends up on disk (in a temp file) and not in memory.
Offhand the plan looks pretty reasonable for a 9-way join. I see that
you have two sorts and five hashes going on here. Each of those will
feel entitled to use as much memory as whatever your -S setting is,
which IIRC is 512K by default. But 3.5meg altogether isn't enough to
explain your memory usage ... unless you are using a larger-than-normal
-S switch?
Also, while the sorts are pretty certain to honor your -S limit (plus or
minus some slop), the hashes are much more probabilistic affairs. If a
table is much larger than the planner guesses, or if the distribution of
tuple values is very uneven, a hash join might use a lot more memory
than -S. It's hard to tell whether this might be happening without a
lot more info than EXPLAIN provides, however. EXPLAIN shows that your
tables being hashed are all pretty small, the largest being
't24 users' at 6955 rows. If that's accurate then I doubt hash overrun
is the explanation...
Anyone have any other ideas?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)
@ 1999-12-03 05:51 Douglas Thomson <dougt@mugc.cc.monash.edu.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Douglas Thomson @ 1999-12-03 05:51 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: dougt@mugc.cc.monash.edu.au; pgsql-interfaces
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Douglas Thomson <dougt@mugc.cc.monash.edu.au> writes:
> > 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!
>
> Well, of course it *is* ... the trick is to make sure that anything
> really large ends up on disk (in a temp file) and not in memory.
What I don't understand is, why would it bother trying to read in
lots of data and sort it when it already has an index sorted in the
required order? I was trying to set up the individual table indexes
so that the join could be done efficiently, and not need either lots
of memory or temporary disk files. Do indexes not get used for joins?
> Offhand the plan looks pretty reasonable for a 9-way join. I see that
> you have two sorts and five hashes going on here. Each of those will
> feel entitled to use as much memory as whatever your -S setting is,
> which IIRC is 512K by default. But 3.5meg altogether isn't enough to
> explain your memory usage ... unless you are using a larger-than-normal
> -S switch?
No, I use the default.
> Also, while the sorts are pretty certain to honor your -S limit (plus or
> minus some slop), the hashes are much more probabilistic affairs. If a
> table is much larger than the planner guesses, or if the distribution of
> tuple values is very uneven, a hash join might use a lot more memory
> than -S. It's hard to tell whether this might be happening without a
> lot more info than EXPLAIN provides, however. EXPLAIN shows that your
> tables being hashed are all pretty small, the largest being
> 't24 users' at 6955 rows. If that's accurate then I doubt hash overrun
> is the explanation...
6955 rows is accurate for that table. Values could well be highly
skewed though. The joining attribute for t24 is a student ID "number"
text field, and the numbers will be grouped.
Doug.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-12-03 05:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-02 23:30 Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT) Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-03 00:41 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-03 02:12 ` Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-03 03:10 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-03 05:51 ` Douglas Thomson <dougt@mugc.cc.monash.edu.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox