From: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Explains of queries to partitioned tables |
Date: | 2010-07-26 08:47:00 |
Message-ID: | 4C4D4B84.8010203@dc.baikal.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
There is a partitioned table with 2 partitions:
drop table if exists p cascade;
create table p (
id bigint not null primary key,
ts timestamp);
create table p_actual ( check (ts is null) ) inherits (p);
create table p_historical ( check (ts is not null) ) inherits (p);
-- I skipped the triggers and rules creation
insert into p (id, ts) values (1, '2000-01-01');
insert into p (id, ts) values (2, null);
insert into p (id, ts) values (3, '2001-01-01');
insert into p (id, ts) values (4, '2005-01-01');
analyze p;
analyze p_actual;
analyze p_historical;
Here is the explain output for the query 'select * from p where ts is null'
Result (cost=0.00..188.10 rows=10 width=16) (actual time=0.028..0.038
rows=1 loops=1)
-> Append (cost=0.00..188.10 rows=10 width=16) (actual
time=0.023..0.029 rows=1 loops=1)
-> Seq Scan on p (cost=0.00..187.00 rows=9 width=16) (actual
time=0.002..0.002 rows=0 loops=1)
Filter: (ts IS NULL)
-> Seq Scan on p_actual p (cost=0.00..1.10 rows=1 width=16)
(actual time=0.014..0.016 rows=1 loops=1)
Filter: (ts IS NULL)
Total runtime: 0.080 ms
You can notice that the optimizer expects 10 rows in the table p and as
a result of this assumption the whole query is estimated as 10 rows.
Whether it will cause a performance impact further? pg_stats does not
contain any statistics on the table 'p'. Is this a cause of such behaviour?
The estimation is worse for some other queries, for example 'select *
from p where ts is not null'
Result (cost=0.00..188.30 rows=1764 width=16) (actual time=0.021..0.049
rows=3 loops=1)
-> Append (cost=0.00..188.30 rows=1764 width=16) (actual
time=0.016..0.032 rows=3 loops=1)
-> Seq Scan on p (cost=0.00..187.00 rows=1761 width=16)
(actual time=0.003..0.003 rows=0 loops=1)
Filter: (ts IS NOT NULL)
-> Seq Scan on p_historical p (cost=0.00..1.30 rows=3
width=16) (actual time=0.008..0.015 rows=3 loops=1)
Filter: (ts IS NOT NULL)
Total runtime: 0.095 ms
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2010-07-26 09:15:12 | Re: Big difference in time returned by EXPLAIN ANALYZE SELECT ... AND SELECT ... |
Previous Message | Piotr Gasidło | 2010-07-26 08:44:15 | Re: Big difference in time returned by EXPLAIN ANALYZE SELECT ... AND SELECT ... |