Query on partitioned table not using index

Hi,

i'm working on a strange behaviour of planner,

PostgreSQL version : 8.4

Stats & vacuum state : just done, the table is never changed after creation ( Create table as...)

Here's my query :

SELECT cabmnt___rfovsnide::varchar FROM zcub_258 WHERE cabmnt___rfovsnide > '201301_reel' ORDER BY cabmnt___rfovsnide LIMIT 1

Here's the table :

The table is partitionned by column  cabmnt___rfovsnide

There is 24 partitions.

CREATE TABLE zcub_258
(
  dwhinvyea character varying(32),
  dwhinvmon text,
  dwhinvmonl character varying(32),
  dwhinvday text,
  mnt_2_rfodst0 character varying,
  mnt_2_rfodst1 character varying,
  mnt_2_rfodst2 character varying,
  mnt_2_rfodst3 character varying,
  mnt_2_rfodst4 character varying,
  nivmnt_2_rfodst integer,
  mnt___rfontr0 character varying,
  mnt___rfontr1 character varying,
  mnt___rfontr2 character varying,
  mnt___rfontr3 character varying,
  mnt___rfontr4 character varying,
  mnt___rfontr5 character varying,
  mnt___rfontr6 character varying,
  mnt___rfontr7 character varying,
  mnt___rfontr8 character varying,
  mnt___rfontr9 character varying,
  nivmnt___rfontr integer,
  cabmnt___rfovsnide character varying(32),
  cabmnt___rteprcide character varying(32),
  cabmnt___rtestdide character varying(32),
  key1 integer,
  key2 integer,the table
  key3 integer,
  q0 numeric,
  nothing integer,
  libmnt_2_rfodst0 character varying(32),
  liblmnt_2_rfodst0 character varying(100),
  libmnt_2_rfodst1 character varying(32),
  liblmnt_2_rfodst1 character varying(100),
  libmnt_2_rfodst2 character varying(32),
  liblmnt_2_rfodst2 character varying(100),
  libmnt_2_rfodst3 character varying(32),
  liblmnt_2_rfodst3 character varying(100),
  libmnt_2_rfodst4 character varying(32),
  liblmnt_2_rfodst4 character varying(100),
  libmnt___rfontr0 character varying(32),
  liblmnt___rfontr0 character varying(100),
  libmnt___rfontr1 character varying(32),
  liblmnt___rfontr1 character varying(100),
  libmnt___rfontr2 character varying(32),
  liblmnt___rfontr2 character varying(100),
  libmnt___rfontr3 character varying(32),
  liblmnt___rfontr3 character varying(100),
  libmnt___rfontr4 character varying(32),
  liblmnt___rfontr4 character varying(100),
  libmnt___rfontr5 character varying(32),
  liblmnt___rfontr5 character varying(100),
  libmnt___rfontr6 character varying(32),
  liblmnt___rfontr6 character varying(100),
  libmnt___rfontr7 character varying(32),
  liblmnt___rfontr7 character varying(100),
  libmnt___rfontr8 character varying(32),
  liblmnt___rfontr8 character varying(100),
  libmnt___rfontr9 character varying(32),
  liblmnt___rfontr9 character varying(100)
)


the plan is :


Limit  (cost=1572842.00..1572842.00 rows=1 width=13)
  ->  Sort  (cost=1572842.00..1619836.83 rows=18797933 width=13)
        Sort Key: public.zcub_143.cabmnt___rfovsnide
        ->  Result  (cost=0.00..1478852.33 rows=18797933 width=13)
              ->  Append  (cost=0.00..1478852.33 rows=18797933 width=13)
                    ->  Seq Scan on zcub_143  (cost=0.00..67.91 rows=3591 width=82)
                    ->  Seq Scan on zcub_143_0 zcub_143  (cost=0.00..21941.36 rows=265936 width=11)
                    ->  Seq Scan on zcub_143_1 zcub_143  (cost=0.00..695.37 rows=8637 width=15)
                    ->  Seq Scan on zcub_143_2 zcub_143  (cost=0.00..36902.82 rows=454482 width=12)
                    ->  Seq Scan on zcub_143_3 zcub_143  (cost=0.00..116775.60 rows=1475460 width=15)
                    ->  Seq Scan on zcub_143_4 zcub_143  (cost=0.00..170064.21 rows=2111521 width=15)
                    ->  Seq Scan on zcub_143_5 zcub_143  (cost=0.00..44583.32 rows=559332 width=12)
                    ->  Seq Scan on zcub_143_6 zcub_143  (cost=0.00..48501.54 rows=608454 width=12)
                    ->  Seq Scan on zcub_143_7 zcub_143  (cost=0.00..53600.30 rows=687630 width=12)
                    ->  Seq Scan on zcub_143_8 zcub_143  (cost=0.00..57048.78 rows=731078 width=12)
                    ->  Seq Scan on zcub_143_9 zcub_143  (cost=0.00..60401.80 rows=773880 width=12)
                    ->  Seq Scan on zcub_143_10 zcub_143  (cost=0.00..64455.42 rows=828942 width=12)
                    ->  Seq Scan on zcub_143_11 zcub_143  (cost=0.00..67903.80 rows=872480 width=12)
                    ->  Seq Scan on zcub_143_12 zcub_143  (cost=0.00..71341.55 rows=915955 width=12)
                    ->  Seq Scan on zcub_143_13 zcub_143  (cost=0.00..74761.82 rows=959182 width=12)
                    ->  Seq Scan on zcub_143_14 zcub_143  (cost=0.00..78838.92 rows=1014292 width=12)
                    ->  Seq Scan on zcub_143_15 zcub_143  (cost=0.00..82330.08 rows=1058208 width=12)
                    ->  Seq Scan on zcub_143_16 zcub_143  (cost=0.00..168486.12 rows=2149712 width=15)
                    ->  Seq Scan on zcub_143_17 zcub_143  (cost=0.00..86700.75 rows=1112575 width=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