| From: | Dave Johansen <davejohansen(at)gmail(dot)com> |
|---|---|
| To: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
| Subject: | MAX() and multi-column index on a partitioned table? |
| Date: | 2015-05-22 22:27:29 |
| Message-ID: | CAAcYxUe0J55EpVhFnbJo6A6FHizt6NzfhH2dRGHobXKvpTPjzQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2015-05-22 22:42:16 | Re: MAX() and multi-column index on a partitioned table? |
| Previous Message | Jim Nasby | 2015-05-22 21:23:19 | Re: Fastest way / best practice to calculate "next birthdays" |