partition query using Seq Scan even when index is present
От | Kenneth Cox |
---|---|
Тема | partition query using Seq Scan even when index is present |
Дата | |
Msg-id | op.uznbv8qi5ru9c3@kent60.office.vivox.com обсуждение исходный текст |
Ответы |
Re: partition query using Seq Scan even when index is
present
Re: partition query using Seq Scan even when index is present |
Список | 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)
В списке pgsql-performance по дате отправления: