Обсуждение: MAX() and multi-column index on a partitioned table?
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);
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);
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)
Dave Johansen <davejohansen@gmail.com> writes:
> 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?
What PG version are you using? 9.1 or newer should know how to do this
with a merge append of several indexscans.
regards, tom lane
On Fri, May 22, 2015 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dave Johansen <davejohansen@gmail.com> writes:
> 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?
What PG version are you using? 9.1 or newer should know how to do this
with a merge append of several indexscans.
Sorry, I should have mentioned that in the original email. I'm using 8.4.20 on RHEL 6. From your reply, it sounds like this is the expected behavior for 8.4 and 9.0. Is that the case?
Thanks,
Dave
Dave Johansen <davejohansen@gmail.com> writes:
> On Fri, May 22, 2015 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What PG version are you using? 9.1 or newer should know how to do this
>> with a merge append of several indexscans.
> Sorry, I should have mentioned that in the original email. I'm using 8.4.20
> on RHEL 6. From your reply, it sounds like this is the expected behavior
> for 8.4 and 9.0. Is that the case?
Yeah, pre-9.1 is not very bright about that. You could factor the query
manually, perhaps, though it's surely a pain in the neck.
regards, tom lane