From: Souquieres Adam <adam.souquieres@axege.com>
To: pgsql-performance@postgresql.org
Subject: Memory-olic query and Materialize
Date: Thu, 12 Sep 2013 12:14:21 +0200
Message-ID: <523193FD.4000307@axege.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi To all Pg performance users,
we've found a strange behaviour in PostgreSQL 9.1.9.
_Here' our server not default configuration :_
default_statistics_target = 100 # pgtune wizard 2011-07-06
maintenance_work_mem = 384MB # pgtune wizard 2011-07-06
constraint_exclusion = on # pgtune wizard 2011-07-06
checkpoint_completion_target = 0.9 # pgtune wizard 2011-07-06
effective_cache_size = 4608MB # pgtune wizard 2011-07-06
work_mem = 36MB # pgtune wizard 2011-07-06
wal_buffers = 8MB # pgtune wizard 2011-07-06
shared_buffers = 1024MB # pgtune wizard 2011-07-06
max_connections = 200 # pgtune wizard 2011-07-06
random_page_cost = 1.5
checkpoint_segments = 20
The server has 16G ram and 16G swap
Here the story :
We have a table witch store some tree data :
CREATE TABLE rfoade
(
rfoade___rforefide character varying(32) NOT NULL, -- Tree Category
rfoade___rfovdeide character varying(32) NOT NULL, -- Tree NAME
rfoade_i_rfodstide character varying(32) NOT NULL, -- Element NAME
rfoadeaxe integer NOT NULL DEFAULT 0, -- ( not interresting here)
rfoadervs integer NOT NULL, -- Tree revision
rfoadenpm integer DEFAULT 1, -- ( not interresting here)
rfoade_s_rfodstide character varying(32) NOT NULL, -- Element Father
rfoadegch character varying(104) NOT NULL DEFAULT '0'::character
varying, -- Left Marker (used for query part of trees)
rfoadedrt character varying(104) NOT NULL DEFAULT '99999'::character
varying, -- Right Marker (used for query part of trees)
rfoadeniv integer NOT NULL DEFAULT 0, -- Depth in trees
rfoadetxt character varying(1500), -- Free text
rfoadenum integer NOT NULL DEFAULT 99999, -- Mathematical data used
for generating left and right markers
rfoadeden integer NOT NULL DEFAULT 999, -- Mathematical data used for
generating left and right markers
rfoadechm character varying(4000) NOT NULL DEFAULT
'INVALID'::character varying, -- String with data about path to this node
rfoadeord integer NOT NULL DEFAULT 999999, -- (order of node in
brotherhood)
CONSTRAINT rfoade_pk PRIMARY KEY (rfoade___rforefide,
rfoade_i_rfodstide, rfoade___rfovdeide, rfoadervs)
USING INDEX TABLESPACE tb_index_axabas,
CONSTRAINT rfoade_fk_ade FOREIGN KEY (rfoade___rforefide,
rfoade___rfovdeide, rfoade_s_rfodstide, rfoadervs) -- Constraint :
father must exist
REFERENCES rfoade (rfoade___rforefide, rfoade___rfovdeide,
rfoade_i_rfodstide, rfoadervs) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT rfoade_fk_vde FOREIGN KEY (rfoade___rforefide,
rfoade___rfovdeide, rfoadervs, rfoadeaxe) -- Constraint : tree must
REFERENCES rfovde (rfovde___rforefide, rfovdeide, rfovdervs,
rfovdeaxe) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT rfoade_int CHECK (rfoadedrt::text > rfoadegch::text),
CONSTRAINT rfoade_ord CHECK (rfoadenum >= rfoadeden)
)
This table is storing all trees of 'elements' in different
organisations, one element can be in many trees
The query witch lead to the evil behaviour is this one : ("analyse
rfoade" was run just before)
insert into rfoade ( rfoadechm, rfoadegch, rfoadedrt, rfoadenum,
rfoadeden, rfoadeniv, rfoade___rforefide, rfoade___rfovdeide, rfoadervs,
rfoade_i_rfodstide, rfoade_s_rfodstide, rfoadetxt, rfoadenpm, rfoadeord,
rfoadeaxe)
SELECT reffils.rfoadechm,
reffils.rfoadegch,
reffils.rfoadedrt,
reffils.rfoadenum,
reffils.rfoadeden,
reffils.rfoadeniv,
reffils.rfoade___rforefide,
'ANA_HORS_CARB_COMB',
1,
reffils.rfoade_i_rfodstide,
reffils.rfoade_s_rfodstide,
reffils.rfoadetxt,
reffils.rfoadenpm,
reffils.rfoadeord,
reffils.rfoadeaxe
FROM rfoade ref
JOIN rfoade reffils
ON reffils.rfoade___rforefide = 'CHUL'
AND reffils.rfoade___rfovdeide = 'UF_SA'
AND reffils.rfoadervs = '1'
AND reffils.rfoadegch > ref.rfoadegch
AND reffils.rfoadedrt < ref.rfoadedrt
WHERE ref.rfoadeniv = 2
AND ref.rfoade___rforefide = 'CHUL'
AND ref.rfoade___rfovdeide = 'UF_SA'
AND ref.rfoadervs = '1'
AND ref.rfoade_i_rfodstide IN (SELECT rfoade_i_rfodstide
FROM rfoade cible
WHERE rfoade___rforefide = 'CHUL'
AND rfoade___rfovdeide =
'ANA_HORS_CARB_COMB'
AND rfoadervs = '1')
This query means : "I want to create in tree ANA_HORS_CARB_COMB all
nodes that are under level 2 of tree UF_SA IF i can found level 2
element in tree ANA_HORS_CARB_COMB)
Tree ANA_HORS_CARB_COMB contains 5k lines, tree UF_SA contains 3k lines.
The whole table with all trees contains 230k lines.
_Here the default PLAN :_
http://explain.depesz.com/s/vnkT
*I can't show you the EXPLAIN ANALYSE of this query because when it
fails, all memory and swap (16G+16G) are used and the query is killed by
OOM KILLER by linux.*
I tried to use :
set enable_material = false;
_I was suspecting the materialize node to generate the problem, here the
new plan : _
http://explain.depesz.com/s/k1Y
The query took 2 seconds without any problems
But it's not over :
i re-enable materialize (set enable_material = true;)
I rerun the query and it runs well this time ( same first plan ).
So i get back to my real application launching the query on the same
database :
the query fails badly another time ( same first plan ), using all my
memory and being killed.
For the moment, i disabling material to run this query in my app, but i
quite sure there's something i've missed.
If any of you have hint about this situation, i would greatly appreciate !
*Thanks for (long) reading !*
Souquières Adam
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adam.souquieres@axege.com
Subject: Re: Memory-olic query and Materialize
In-Reply-To: <523193FD.4000307@axege.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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