Memory-olic query and Materialize

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