Explains of queries to partitioned tables
От | Vlad Arkhipov |
---|---|
Тема | Explains of queries to partitioned tables |
Дата | |
Msg-id | 4C4D4B84.8010203@dc.baikal.ru обсуждение исходный текст |
Ответы |
Re: Explains of queries to partitioned tables
|
Список | 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
В списке pgsql-performance по дате отправления: