Re: Strange explain on partitioned tables - Mailing list pgsql-performance

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