Skip site navigation (1) Skip section navigation (2)

Re: Strange explain on partitioned tables

From: Philippe Rimbault <primbault(at)edd(dot)fr>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Strange explain on partitioned tables
Date: 2010-07-22 10:03:12
Message-ID: 4C481760.7080003@edd.fr (view raw or flat)
Thread:
Lists: pgsql-performance
FYI

I've just installed Postgresql 9 beta 3 (9.0beta3 on i686-pc-linux-gnu, 
compiled by GCC gcc (Debian 4.4.4-6) 4.4.4, 32-bit)

After a pg_upgrade  + vacuum analyze, i've got the following results :

Query on primary table :
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=70356.46..70356.48 rows=5 width=23) (actual 
time=2362.268..2362.271 rows=5 loops=1)"
"  ->  Sort  (cost=70356.46..72991.29 rows=1053932 width=23) (actual 
time=2362.267..2362.269 rows=5 loops=1)"
"        Sort Key: public.documents.id"
"        Sort Method:  top-N heapsort  Memory: 17kB"
"        ->  Nested Loop  (cost=1.52..52851.03 rows=1053932 width=23) 
(actual time=0.062..1912.826 rows=1053929 loops=1)"
"              ->  Index Scan using pk_sources on sources  
(cost=0.00..8.27 rows=1 width=8) (actual time=0.006..0.009 rows=1 loops=1)"
"                    Index Cond: (id = 113)"
"              ->  Hash Join  (cost=1.52..42303.44 rows=1053932 
width=21) (actual time=0.052..1490.353 rows=1053929 loops=1)"
"                    Hash Cond: (public.documents.id_location = l.id)"
"                    ->  Append  (cost=0.00..27810.36 rows=1053932 
width=13) (actual time=0.027..842.627 rows=1053929 loops=1)"
"                          ->  Seq Scan on documents  (cost=0.00..18.25 
rows=3 width=39) (actual time=0.000..0.000 rows=0 loops=1)"
"                                Filter: (id_source = 113)"
"                          ->  Seq Scan on documents_mond documents  
(cost=0.00..27792.11 rows=1053929 width=13) (actual time=0.025..497.517 
rows=1053929 loops=1)"
"                                Filter: (id_source = 113)"
"                    ->  Hash  (cost=1.23..1.23 rows=23 width=10) 
(actual time=0.018..0.018 rows=23 loops=1)"
"                          Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                          ->  Seq Scan on locations l  (cost=0.00..1.23 
rows=23 width=10) (actual time=0.001..0.010 rows=23 loops=1)"
"Total runtime: 2362.369 ms"


On inherted table :
     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..1.81 rows=5 width=23) (actual time=0.033..0.056 
rows=5 loops=1)"
"  ->  Nested Loop  (cost=0.00..381351.92 rows=1053929 width=23) (actual 
time=0.032..0.052 rows=5 loops=1)"
"        ->  Nested Loop  (cost=0.00..368169.54 rows=1053929 width=21) 
(actual time=0.023..0.037 rows=5 loops=1)"
"              ->  Index Scan Backward using idx_documents_mond_id on 
documents_mond documents  (cost=0.00..72973.11 rows=1053929 width=13) 
(actual time=0.014..0.017 rows=5 loops=1)"
"                    Filter: (id_source = 113)"
"              ->  Index Scan using locations_pkey on locations l  
(cost=0.00..0.27 rows=1 width=10) (actual time=0.002..0.003 rows=1 loops=5)"
"                    Index Cond: (l.id = documents.id_location)"
"        ->  Materialize  (cost=0.00..8.27 rows=1 width=8) (actual 
time=0.002..0.002 rows=1 loops=5)"
"              ->  Index Scan using pk_sources on sources  
(cost=0.00..8.27 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)"
"                    Index Cond: (id = 113)"
"Total runtime: 0.095 ms"


Results are better than 8.4 if query is on inherted table but worth if 
query is on primary table.

So waiting for 9.0 will not help me so much ! :)



On 22/07/2010 10:57, Philippe Rimbault wrote:
> Oups! searching on the mailing list show me that it's a known problem ...
>
> http://archives.postgresql.org/pgsql-performance/2010-07/msg00063.php
>
> sorry !
>
>
>
> On 22/07/2010 09:52, Philippe Rimbault wrote:
>>
>> 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
>>
>>
>
>


In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2010-07-22 13:32:36
Subject: Re: Strange explain on partitioned tables
Previous:From: Rob WultschDate: 2010-07-22 09:09:21
Subject: Re: [PERFORM] Using more tha one index per table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group