Message-ID: <4C4D4B84.8010203@dc.baikal.ru>
Date: Mon, 26 Jul 2010 17:47:00 +0900
From: Vlad Arkhipov <arhipov@dc.baikal.ru>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Explains of queries to partitioned tables
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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