MAX() and multi-column index on a partitioned table?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm trying to call MAX() on the first value of a multi-column index of a partitioned table and the planner is choosing to do a sequential scan instead of an index scan. Is there something I can do to fix this?

Here's a simplified version of our schema:
CREATE TABLE data ( tutci DOUBLE PRECISION, tutcf DOUBLE PRECISION, value INTEGER );
CREATE TABLE data1 ( CHECK ( tutci >= 1000 AND tutci < 2000 ) ) INHERITS (data);
CREATE TABLE data2 ( CHECK ( tutci >= 2000 AND tutci < 3000 ) ) INHERITS (data);
With the following indexes:
CREATE INDEX data_tutc_index ON data(tutci, tutcf);
CREATE INDEX data1_tutc_index ON data1(tutci, tutcf);
CREATE INDEX data2_tutc_index ON data2(tutci, tutcf);

No data is stored in the parent table (only in the partitions) and the explain is as follows after doing a CLUSTER on the index and a VACUUM ANALYZE after populating with simple test data:
EXPLAIN SELECT MAX(tutci) FROM data;
                                 QUERY PLAN                                
----------------------------------------------------------------------------
 Aggregate  (cost=408.53..408.54 rows=1 width=8)
   ->  Append  (cost=0.00..354.42 rows=21642 width=8)
         ->  Seq Scan on data  (cost=0.00..26.30 rows=1630 width=8)
         ->  Seq Scan on data1 data  (cost=0.00..164.11 rows=10011 width=8)
         ->  Seq Scan on data2 data  (cost=0.00..164.01 rows=10001 width=8)

Thanks,
Dave

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux