pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMAX() and multi-column index on a partitioned table?
4+ messages / 2 participants
[nested] [flat]
* MAX() and multi-column index on a partitioned table?
@ 2015-05-22 22:27 Dave Johansen <davejohansen@gmail.com>
2015-05-22 22:42 ` Re: MAX() and multi-column index on a partitioned table? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Dave Johansen @ 2015-05-22 22:27 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MAX() and multi-column index on a partitioned table?
2015-05-22 22:27 MAX() and multi-column index on a partitioned table? Dave Johansen <davejohansen@gmail.com>
@ 2015-05-22 22:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-22 23:53 ` Re: MAX() and multi-column index on a partitioned table? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2015-05-22 22:42 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MAX() and multi-column index on a partitioned table?
2015-05-22 22:27 MAX() and multi-column index on a partitioned table? Dave Johansen <davejohansen@gmail.com>
2015-05-22 22:42 ` Re: MAX() and multi-column index on a partitioned table? Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-05-22 23:53 ` Dave Johansen <davejohansen@gmail.com>
2015-05-23 01:13 ` Re: MAX() and multi-column index on a partitioned table? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Dave Johansen @ 2015-05-22 23:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MAX() and multi-column index on a partitioned table?
2015-05-22 22:27 MAX() and multi-column index on a partitioned table? Dave Johansen <davejohansen@gmail.com>
2015-05-22 22:42 ` Re: MAX() and multi-column index on a partitioned table? Tom Lane <tgl@sss.pgh.pa.us>
2015-05-22 23:53 ` Re: MAX() and multi-column index on a partitioned table? Dave Johansen <davejohansen@gmail.com>
@ 2015-05-23 01:13 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2015-05-23 01:13 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-05-23 01:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-22 22:27 MAX() and multi-column index on a partitioned table? Dave Johansen <davejohansen@gmail.com>
2015-05-22 22:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-22 23:53 ` Dave Johansen <davejohansen@gmail.com>
2015-05-23 01:13 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox