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