Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VK3ux-00059w-Rx for pgsql-performance@arkaria.postgresql.org; Thu, 12 Sep 2013 10:14:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VK3uw-0004dr-Ce for pgsql-performance@arkaria.postgresql.org; Thu, 12 Sep 2013 10:14:30 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VK3uu-0004dj-V1 for pgsql-performance@postgresql.org; Thu, 12 Sep 2013 10:14:29 +0000 Received: from smtp09.smtpout.orange.fr ([80.12.242.131] helo=smtp.smtpout.orange.fr) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VK3ur-0000wL-9L for pgsql-performance@postgresql.org; Thu, 12 Sep 2013 10:14:28 +0000 Received: from axege.com ([82.127.232.162]) by mwinf5d32 with ME id QAEP1m00R3WtTCJ03AEPQ5; Thu, 12 Sep 2013 12:14:24 +0200 Received: from localhost (localhost [127.0.0.1]) by axege.com (Postfix) with ESMTP id 6733043DE03 for ; Thu, 12 Sep 2013 12:14:23 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at myhostname="axege.com." Received: from axege.com ([127.0.0.1]) by localhost (axege.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id jGEAoE4dmGHB for ; Thu, 12 Sep 2013 12:14:21 +0200 (CEST) Received: from [10.0.0.56] (unknown [10.0.0.56]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by axege.com (Postfix) with ESMTPSA id 8F00343DE02 for ; Thu, 12 Sep 2013 12:14:21 +0200 (CEST) Message-ID: <523193FD.4000307@axege.com> Date: Thu, 12 Sep 2013 12:14:21 +0200 From: Souquieres Adam User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130803 Thunderbird/17.0.8 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Memory-olic query and Materialize Content-Type: multipart/alternative; boundary="------------090001040403040600080001" X-Pg-Spam-Score: 1.6 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------090001040403040600080001 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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 --------------090001040403040600080001 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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

--------------090001040403040600080001--