From: | "Kenneth Cox" <kenstir(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | partition query using Seq Scan even when index is present |
Date: | 2009-09-02 20:15:34 |
Message-ID: | op.uznbv8qi5ru9c3@kent60.office.vivox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Jaman | 2009-09-02 20:31:29 | Re: partition query using Seq Scan even when index is present |
Previous Message | Scott Carey | 2009-09-02 18:28:51 | Re: partition queries hitting all partitions even though check key is specified |