aggregate planning with partitions

From: "Jonathan Ellis" <jonathan(at)utahpython(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: aggregate planning with partitions
Date: 2007-03-05 16:25:40
Message-ID: e06563880703050825m194267dak43c58d6c104453e8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table, "connection_events", partitioned via inheritance on a
column "logtime." I'm running 8.2. Mostly the partitioning works
pretty well. I have noticed though that the query

select max(logtime) from connection_events

always uses seq_scan across all partitions, which is painful.
(Rephrasing it as a limit query does not change the plan.)

I've run analyze, and pg can tell that in a single partition using the
logtime index is the right thing to do, but it can't when the main
table is queried.

I can write a function to force index use by querying each partition
separately but I wanted to know if (a) there is a simpler solution I'm
missing and (b) if there is a generalizable principle here that can
help me avoid problems with the planner in the future. (From my
single data point I would guess "avoid aggregates" but that may be
oversimplified.)

Thanks,

-Jonathan

Browse pgsql-general by date

  From Date Subject
Next Message Omar Eljumaily 2007-03-05 16:32:23 Re: real multi-master replication?
Previous Message Heikki Linnakangas 2007-03-05 16:25:27 Re: PostgreSQL 8.2.3 VACUUM Timings/Performance