Yep.... I ran into the exact same problem. My solution was to create a pl/pgsql function to query the child tables: ( http://archives.postgresql.org/pgsql-performance/2008-11/msg00284.php) If you find a better solution please share.
-Greg Jaman On Wed, Sep 2, 2009 at 1:15 PM, Kenneth Cox <kens...@gmail.com> wrote: > With postgresql-8.3.6, I have many partitions inheriting a table. SELECT > min() on the parent performs a Seq Scan, but SELECT min() on a child uses > the index. Is this another case where the planner is not aware enough to > come up with the best plan? I tried creating an index on the parent table > to no avail. Is there a way to formulate the query so that it uses the > index? Here is the general flavor: > > create table calls (caller text, ts timestamptz); > create table calls_partition_2009_08 (check (ts >= '2009-08-01' and ts < > '2009-09-01')) inherits (calls); > create index calls_partition_2009_08_ts on calls_partition_2009_08 (ts); > insert into calls_partition_2009_08 (ts) > select to_timestamp(unix_time) > from generate_series(extract(epoch from '2009-08-01'::timestamptz)::int, > extract(epoch from '2009-08-31 > 23:59'::timestamptz)::int, 60) as unix_time; > analyze calls_partition_2009_08; > explain select min(ts) from calls; > > QUERY PLAN > > ----------------------------------------------------------------------------------------------- > Aggregate (cost=780.50..780.51 rows=1 width=8) > -> Append (cost=0.00..666.00 rows=45800 width=8) > -> Seq Scan on calls (cost=0.00..21.60 rows=1160 width=8) > -> Seq Scan on calls_partition_2009_08 calls (cost=0.00..644.40 > rows=44640 width=8) > (4 rows) > > explain select min(ts) from calls_partition_2009_08; > > QUERY PLAN > > ------------------------------------------------------------------------------------------------------------------------------- > Result (cost=0.03..0.04 rows=1 width=0) > InitPlan > -> Limit (cost=0.00..0.03 rows=1 width=8) > -> Index Scan using calls_partition_2009_08_ts on > calls_partition_2009_08 (cost=0.00..1366.85 rows=44640 width=8) > Filter: (ts IS NOT NULL) > (5 rows) > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
