Very slow planning performance on partition table
От | Rural Hunter |
---|---|
Тема | Very slow planning performance on partition table |
Дата | |
Msg-id | 53CFB6BC.4050606@gmail.com обсуждение исходный текст |
Ответы |
Re: Very slow planning performance on partition table
Re: Very slow planning performance on partition table |
Список | pgsql-performance |
Hi, I have a table partitioned with about 60 children tables. Now I found the planning time of simple query with partition key are very slow. # explain analyze select count(*) as cnt from article where pid=88 and hash_code='2ca3ff8b17b163f0212c2ba01b80a064'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=16.55..16.56 rows=1 width=0) (actual time=0.259..0.259 rows=1 loops=1) -> Append (cost=0.00..16.55 rows=2 width=0) (actual time=0.248..0.250 rows=1 loops=1) -> Seq Scan on article (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1) Filter: ((pid = 88) AND (hash_code = '2ca3ff8b17b163f0212c2ba01b80a064'::bpchar)) -> Index Scan using article_88_hash_idx on article_88 article (cost=0.00..16.55 rows=1 width=0) (actual time=0.246..0.248 rows=1 loops=1) Index Cond: (hash_code = '2ca3ff8b17b163f0212c2ba01b80a064'::bpchar) Filter: (pid = 88) Total runtime: 3.816 ms (8 rows) Time: 30999.986 ms You can see the timing output that the actual run time of the 'explain analyze' is 30 seconds while the select sql itself takes only 3 ms. My partition key is on article.pid and the constraint is simple like this: CONSTRAINT article_88_pid_check CHECK (pid = 88). What's wrong and how can I improve the planning performance?
В списке pgsql-performance по дате отправления: