Skip site navigation (1) Skip section navigation (2)

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

From: Greg Jaman <gjaman(at)gmail(dot)com>
To: Kenneth Cox <kenstir(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: partition query using Seq Scan even when index is present
Date: 2009-09-02 20:31:29
Message-ID: b72893ad0909021331q45fe1a9gb74d0950ff03326a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

In response to

Responses

pgsql-performance by date

Next:From: Scott OtisDate: 2009-09-02 20:44:42
Subject: Seeking performance advice and explanation for high I/O on 8.3
Previous:From: Kenneth CoxDate: 2009-09-02 20:15:34
Subject: partition query using Seq Scan even when index is present

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group