Re: partition query using Seq Scan even when index is present

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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 <kenstir@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux