Message-ID: <4C47F8C8.10005@edd.fr>
Date: Thu, 22 Jul 2010 09:52:40 +0200
From: Philippe Rimbault <primbault@edd.fr>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.10) Gecko/20100619 Icedove/3.0.5
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Strange explain on partitioned tables
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Hi all,

I'm using Postgresql 8.4.4 on Debian.
In postgresql.conf, constraint_exclusion is set to "on"

I have partitioned tables with check constraints.
My primary table :
     CREATE TABLE documents
     (
         id serial NOT NULL,
         id_source smallint,
         nod integer,
         num text,
         id_fourniture integer,
         dav date NOT NULL,
         maj timestamp without time zone NOT NULL DEFAULT now(),
         id_location "char",
         id_partition smallint,
         mark text
     );

There is no row in "only" documents :
SQL> select count(*) from only documents;
     -> 0
SQL> select count(*) from documents;
     -> 160155756

I have one thousand inherited tables like this one (with a different 
check constraint on each) :
     CREATE TABLE documents_mond
     (
         CONSTRAINT documents_mond_id_source_check CHECK (id_source = 113)
     )
     INHERITS (documents);
     CREATE INDEX idx_documents_mond_id
     ON documents_mond
     USING btree
     (id);

     CREATE INDEX idx_documents_mond_id_partition
     ON documents_mond
     USING btree
     (id_partition);

     CREATE INDEX idx_documents_mond_id_source_dav
     ON documents_mond
     USING btree
     (id_source, dav);
     ALTER TABLE documents_mond CLUSTER ON idx_documents_mond_id_source_dav;

     CREATE INDEX idx_documents_mond_id_source_nod
     ON documents_mond
     USING btree
     (id_source, nod);

     CREATE INDEX idx_documents_mond_id_source_num
     ON documents_mond
     USING btree
     (id_source, num);

     CREATE INDEX idx_documents_mond_maj
     ON documents_mond
     USING btree
     (maj);

SQL> select count(*) from documents_mond;
     -> 1053929

When i perform this query on the primary table :
EXPLAIN ANALYZE
     select
         documents.id,
         documents.num,
         sources.name,
         l.name
     from
         documents,
         locations l,
         sources
     where
         documents.id_source = 113 and
         documents.id_location=l.id and
         documents.id_source=sources.id
     order by
         documents.id desc
     limit 5;
"Limit  (cost=36209.55..36209.57 rows=5 width=24) (actual 
time=2307.181..2307.185 rows=5 loops=1)"
"  ->  Sort  (cost=36209.55..36512.56 rows=121202 width=24) (actual 
time=2307.180..2307.180 rows=5 loops=1)"
"        Sort Key: public.documents.id"
"        Sort Method:  top-N heapsort  Memory: 17kB"
"        ->  Nested Loop  (cost=1.52..34196.43 rows=121202 width=24) 
(actual time=0.076..1878.189 rows=1053929 loops=1)"
"              ->  Index Scan using pk_sources on sources  
(cost=0.00..8.27 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1)"
"                    Index Cond: (id = 113)"
"              ->  Hash Join  (cost=1.52..32976.15 rows=121202 width=22) 
(actual time=0.059..1468.982 rows=1053929 loops=1)"
"                    Hash Cond: (public.documents.id_location = l.id)"
"                    ->  Append  (cost=0.00..27810.36 rows=1053932 
width=14) (actual time=0.031..836.280 rows=1053929 loops=1)"
"                          ->  Seq Scan on documents  (cost=0.00..18.25 
rows=3 width=39) (actual time=0.001..0.001 rows=0 loops=1)"
"                                Filter: (id_source = 113)"
"                          ->  Seq Scan on documents_mond documents  
(cost=0.00..27792.11 rows=1053929 width=14) (actual time=0.030..503.815 
rows=1053929 loops=1)"
"                                Filter: (id_source = 113)"
"                    ->  Hash  (cost=1.23..1.23 rows=23 width=10) 
(actual time=0.019..0.019 rows=23 loops=1)"
"                          ->  Seq Scan on locations l  (cost=0.00..1.23 
rows=23 width=10) (actual time=0.001..0.007 rows=23 loops=1)"
"Total runtime: 2307.498 ms"

And when i perform the same query directly on the inherited table (CHECK 
id_source=113) :
EXPLAIN ANALYZE
     select
         documents.id,
         documents.num,
         sources.name,
         l.name
     from
         documents_mond documents,
         locations l,
         sources
     where
         documents.id_source = 113 and
         documents.id_location=l.id and
         documents.id_source=sources.id
     order by
         documents.id desc
     limit 5;
"Limit  (cost=0.00..43.13 rows=5 width=24) (actual time=0.024..0.050 
rows=5 loops=1)"
"  ->  Nested Loop  (cost=0.00..9091234.75 rows=1053929 width=24) 
(actual time=0.023..0.049 rows=5 loops=1)"
"        ->  Nested Loop  (cost=0.00..8796038.31 rows=1053929 width=16) 
(actual time=0.020..0.035 rows=5 loops=1)"
"              ->  Index Scan Backward using idx_documents_mond_id on 
documents_mond documents  (cost=0.00..71930.23 rows=1053929 width=14) 
(actual time=0.012..0.015 rows=5 loops=1)"
"                    Filter: (id_source = 113)"
"              ->  Index Scan using pk_sources on sources  
(cost=0.00..8.27 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=5)"
"                    Index Cond: (sources.id = 113)"
"        ->  Index Scan using locations_pkey on locations l  
(cost=0.00..0.27 rows=1 width=10) (actual time=0.001..0.002 rows=1 loops=5)"
"              Index Cond: (l.id = documents.id_location)"
"Total runtime: 0.086 ms"

OR

EXPLAIN ANALYZE
     select
         documents.id,
         documents.num,
         sources.name,
         l.name
     from
         documents_mond documents,
         locations l,
         sources
     where
         /* documents.id_source = 113 and */
         documents.id_location=l.id and
         documents.id_source=sources.id
     order by
         documents.id desc
     limit 5;
"Limit  (cost=0.00..3.13 rows=5 width=24) (actual time=0.025..0.052 
rows=5 loops=1)"
"  ->  Nested Loop  (cost=0.00..659850.75 rows=1053929 width=24) (actual 
time=0.024..0.051 rows=5 loops=1)"
"        ->  Nested Loop  (cost=0.00..364654.31 rows=1053929 width=16) 
(actual time=0.021..0.037 rows=5 loops=1)"
"              ->  Index Scan Backward using idx_documents_mond_id on 
documents_mond documents  (cost=0.00..69295.41 rows=1053929 width=14) 
(actual time=0.011..0.013 rows=5 loops=1)"
"              ->  Index Scan using pk_sources on sources  
(cost=0.00..0.27 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=5)"
"                    Index Cond: (sources.id = documents.id_source)"
"        ->  Index Scan using locations_pkey on locations l  
(cost=0.00..0.27 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=5)"
"              Index Cond: (l.id = documents.id_location)"
"Total runtime: 0.091 ms"

Is it a normal behavior ?
I need to rewrite all my Perl scripts to have query pointing only on 
inherited tables (when possible) ?
I was thinking that query pointing on primary table were correctly 
dispatched on inherited tables ... I missing something ?

Regards

Philippe


Ps : I'm french, so my english is approximate ... hoping it's understandable