Re: Strange explain on partitioned tables

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
>>
>>
>
>