Memory-olic query and Materialize
От | Souquieres Adam |
---|---|
Тема | Memory-olic query and Materialize |
Дата | |
Msg-id | 523193FD.4000307@axege.com обсуждение исходный текст |
Список | pgsql-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
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
В списке pgsql-performance по дате отправления:
Следующее
От: Mikkel LauritsenДата:
Сообщение: Re: Reasons for choosing one execution plan overanother?