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

From: Merlin Moncure <mmoncure(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-03 14:49:36
Message-ID: b42b73150909030749h3ab95d05sa59b5a5545e376fc@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Wed, Sep 2, 2009 at 4: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;

ATM, constraint exclusion mainly only supports queries of the form:
SELECT ... WHERE 'x', with x being an expression in the check
constraint. Table partitioning unfortunately is not a free lunch, you
have to be aware of it at all times when writing queries vs your
partitioned tables.

merlin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Andy Colson 2009-09-03 15:02:56 Re: Seeking performance advice and explanation for high I/O on 8.3
Previous Message Tom Lane 2009-09-03 14:32:04 Re: Help: how to speed up query after db server reboot