Re: Strange explain on partitioned tables
От | Philippe Rimbault |
---|---|
Тема | Re: Strange explain on partitioned tables |
Дата | |
Msg-id | 4C480816.5030406@edd.fr обсуждение исходный текст |
Ответ на | Strange explain on partitioned tables (Philippe Rimbault <primbault@edd.fr>) |
Ответы |
Re: Strange explain on partitioned tables
(Philippe Rimbault <primbault@edd.fr>)
|
Список | 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 > >
В списке pgsql-performance по дате отправления: