[PERFORM] Query on partitioned table not using index

[Souquieres Adam]

dth=12)
->  Seq Scan on zcub_143_18 zcub_143 
(cost=0.00..25063.32 rows=302332 width=14)
->  Seq Scan on zcub_143_19 zcub_143 
(cost=0.00..47830.92 rows=614292 width=12)
->  Seq Scan on zcub_143_20 zcub_143 
(cost=0.00..47832.18 rows=614318 width=12)
->  Seq Scan on zcub_143_21 zcub_143 
(cost=0.00..51906.06 rows=665406 width=12)
->  Seq Scan on zcub_143_22 zcub_143 
(cost=0.00..818.38 rows=10238 width=5)


The query takes few minutes...

_Our observation till now :_

-> since the cabmnt___rfovsnide is the partition key, there is only one 
value by partition
-> we have an index on all partition on cabmnt___rfovsnide : why dont 
postgres use it ?


We have a test environment with similar data and configuration*in 
version 9.1*, and the same query is under 1ms, the plan is not same, it 
use index on all partition and keep only one row from each.


Is this behaviour quite logic in 8.4 ?

Thank you for your time.

Souquieres Adam

[PERFORM] Memory-olic query and Materialize

[Souquieres Adam]

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 '9'::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 9, -- 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 99, -- (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 
O