Re: Strange explain on partitioned tables

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