Memory-olic query and Materialize - Mailing list 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