BUG #16109: Postgres planning time is high across version - 10.6 vs 10.10
От | PG Bug reporting form |
---|---|
Тема | BUG #16109: Postgres planning time is high across version - 10.6 vs 10.10 |
Дата | |
Msg-id | 16109-26a1a88651e90608@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #16109: Postgres planning time is high across version - 10.6vs 10.10
(Andres Freund <andres@anarazel.de>)
Re: BUG #16109: Postgres planning time is high across version -10.6 vs 10.10 (Mukesh Chhatani <chhatani.mukesh@gmail.com>) |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 16109 Logged by: Mukesh Chhatani Email address: chhatani.mukesh@gmail.com PostgreSQL version: 10.10 Operating system: AWS RDS Description: Hello Team, I am experiencing weird issue around planning time for the queries across couple of environments below is the sample of the execution plan Fast Execution Plan transformations=> explain (analyze, buffers) SELECT x2.x3, x2.x4, x2.x5, x2.x6, x2.x7, x2.x8, x2.x9, x2.x10, x2.x11, x2.x12, x2.x13, x2.x14, x2.x15, x2.x16, x2.x17, x2.x18, x2.x19, x2.x20, x2.x21, x2.x22, x2.x23, x2.x24, x2.x25, x2.x26, x2.x27, x2.x28, x2.x29, x2.x30, x2.x31, x2.x32, x2.x33, x2.x34, x35. "provider_id", x35. "provider_phone_id", x35. "provider_id", x35. "address_id", x35. "prod_code", x35. "phone_number", x35. "phone_type", x36. "provider_id", x36. "provider_id", x36. "address_id", x36. "language_code", x36. "language_used_by" FROM ( SELECT x37.x38 AS x14, x37.x39 AS x6, x37.x40 AS x26, x37.x41 AS x9, x37.x42 AS x20, x37.x43 AS x16, x37.x44 AS x8, x37.x45 AS x19, x37.x46 AS x3, x37.x47 AS x13, x37.x48 AS x12, x37.x49 AS x18, x37.x50 AS x17, x37.x51 AS x11, x37.x52 AS x22, x37.x53 AS x21, x37.x54 AS x10, x37.x55 AS x5, x37.x56 AS x4, x37.x57 AS x25, x37.x58 AS x7, x37.x59 AS x15, x37.x60 AS x24, x37.x61 AS x23, ( CASE WHEN (x62. "attribute_value" IS NULL) THEN NULL ELSE 1 END) AS x27, x62. "paid" AS x28, x62. "attribute_value" AS x34, x62. "attribute_id" AS x33, x62. "provider_id" AS x29, x62. "attribute_group_id" AS x32, x62. "parent_paid" AS x31, x62. "address_id" AS x30 FROM ( SELECT "provider_id" AS x46, "zip" AS x38, "first_name" AS x39, "provider_name_id" AS x40, "degree" AS x41, "preferred_flag" AS x42, "county" AS x43, "suffix" AS x44, "individual_id" AS x45, "state" AS x47, "city" AS x48, "latitude" AS x49, "longitude" AS x50, "address" AS x51, "exclusion_type_id" AS x52, "quality_score" AS x53, "gender" AS x54, "last_name" AS x55, "address_id" AS x56, "hi_q_hospital_id" AS x57, "middle_name" AS x58, "zip4" AS x59, "handicap_accessible" AS x60, "sour_address" AS x61 FROM "provider" WHERE "provider_id" = '03563735-3798-441a-aea6-4e561ea347f7') x37 LEFT OUTER JOIN "provider_attribute" x62 ON (x37.x46 = x62. "provider_id") AND (x37.x56 = x62. "address_id")) x2 LEFT OUTER JOIN "provider_phone" x35 ON (x2.x3 = x35. "provider_id") AND (x2.x4 = x35. "address_id") LEFT OUTER JOIN "provider_language" x36 ON (x2.x3 = x36. "provider_id") AND (x2.x4 = x36. "address_id"); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Left Join (cost=15.87..16.25 rows=13 width=920) (actual time=0.021..0.022 rows=0 loops=1) Merge Cond: ((provider.address_id)::text = (x36.address_id)::text) Join Filter: ((provider.provider_id)::text = (x36.provider_id)::text) Buffers: shared hit=3 -> Merge Left Join (cost=12.37..12.69 rows=13 width=754) (actual time=0.021..0.021 rows=0 loops=1) Merge Cond: ((provider.address_id)::text = (x35.address_id)::text) Join Filter: ((provider.provider_id)::text = (x35.provider_id)::text) Buffers: shared hit=3 -> Merge Left Join (cost=8.38..8.59 rows=13 width=584) (actual time=0.021..0.021 rows=0 loops=1) Merge Cond: ((provider.address_id)::text = (x62.address_id)::text) Join Filter: ((provider.provider_id)::text = (x62.provider_id)::text) Buffers: shared hit=3 -> Sort (cost=3.89..3.93 rows=13 width=387) (actual time=0.020..0.021 rows=0 loops=1) Sort Key: provider.address_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=3 -> Index Scan using provider_provider_id_idx on provider (cost=0.42..3.65 rows=13 width=387) (actual time=0.017..0.017 rows=0 loops=1) Index Cond: ((provider_id)::text = '03563735-3798-441a-aea6-4e561ea347f7'::text) Buffers: shared hit=3 -> Sort (cost=4.49..4.56 rows=26 width=197) (never executed) Sort Key: x62.address_id -> Append (cost=0.42..3.88 rows=26 width=197) (never executed) -> Index Scan using provider_attribute_sub_0_provider_id_idx on provider_attribute_sub_0 x62 (cost=0.42..3.88 rows=26 width=197) (never executed) Index Cond: ((provider_id)::text = '03563735-3798-441a-aea6-4e561ea347f7'::text) -> Sort (cost=3.98..4.02 rows=15 width=170) (never executed) Sort Key: x35.address_id -> Index Scan using provider_phone_provider_id_idx on provider_phone x35 (cost=0.43..3.69 rows=15 width=170) (never executed) Index Cond: ((provider_id)::text = '03563735-3798-441a-aea6-4e561ea347f7'::text) -> Sort (cost=3.50..3.51 rows=3 width=88) (never executed) Sort Key: x36.address_id -> Index Scan using provider_language_provider_id_idx on provider_language x36 (cost=0.42..3.47 rows=3 width=88) (never executed) Index Cond: ((provider_id)::text = '03563735-3798-441a-aea6-4e561ea347f7'::text) Planning time: 7.416 ms Execution time: 0.096 ms (34 rows) Slow Execution Plan transformations_uhc_medicaid=> explain (analyze, buffers) SELECT x2.x3, x2.x4, x2.x5, x2.x6, x2.x7, x2.x8, x2.x9, x2.x10, x2.x11, x2.x12, x2.x13, x2.x14, x2.x15, x2.x16, x2.x17, x2.x18, x2.x19, x2.x20, x2.x21, x2.x22, x2.x23, x2.x24, x2.x25, x2.x26, x2.x27, x2.x28, x2.x29, x2.x30, x2.x31, x2.x32, x2.x33, x2.x34, x35. "provider_id", x35. "provider_phone_id", x35. "provider_id", x35. "address_id", x35. "prod_code", x35. "phone_number", x35. "phone_type", x36. "provider_id", x36. "provider_id", x36. "address_id", x36. "language_code", x36. "language_used_by" FROM ( SELECT x37.x38 AS x14, x37.x39 AS x6, x37.x40 AS x26, x37.x41 AS x9, x37.x42 AS x20, x37.x43 AS x16, x37.x44 AS x8, x37.x45 AS x19, x37.x46 AS x3, x37.x47 AS x13, x37.x48 AS x12, x37.x49 AS x18, x37.x50 AS x17, x37.x51 AS x11, x37.x52 AS x22, x37.x53 AS x21, x37.x54 AS x10, x37.x55 AS x5, x37.x56 AS x4, x37.x57 AS x25, x37.x58 AS x7, x37.x59 AS x15, x37.x60 AS x24, x37.x61 AS x23, ( CASE WHEN (x62. "attribute_value" IS NULL) THEN NULL ELSE 1 END) AS x27, x62. "paid" AS x28, x62. "attribute_value" AS x34, x62. "attribute_id" AS x33, x62. "provider_id" AS x29, x62. "attribute_group_id" AS x32, x62. "parent_paid" AS x31, x62. "address_id" AS x30 FROM ( SELECT "provider_id" AS x46, "zip" AS x38, "first_name" AS x39, "provider_name_id" AS x40, "degree" AS x41, "preferred_flag" AS x42, "county" AS x43, "suffix" AS x44, "individual_id" AS x45, "state" AS x47, "city" AS x48, "latitude" AS x49, "longitude" AS x50, "address" AS x51, "exclusion_type_id" AS x52, "quality_score" AS x53, "gender" AS x54, "last_name" AS x55, "address_id" AS x56, "hi_q_hospital_id" AS x57, "middle_name" AS x58, "zip4" AS x59, "handicap_accessible" AS x60, "sour_address" AS x61 FROM "provider" WHERE "provider_id" = '03563735-3798-441a-aea6-4e561ea347f7') x37 LEFT OUTER JOIN "provider_attribute" x62 ON (x37.x46 = x62. "provider_id") AND (x37.x56 = x62. "address_id")) x2 LEFT OUTER JOIN "provider_phone" x35 ON (x2.x3 = x35. "provider_id") AND (x2.x4 = x35. "address_id") LEFT OUTER JOIN "provider_language" x36 ON (x2.x3 = x36. "provider_id") AND (x2.x4 = x36. "address_id"); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=5.14..15.03 rows=2 width=944) (actual time=0.039..0.039 rows=0 loops=1) Join Filter: (((provider.provider_id)::text = (x36.provider_id)::text) AND ((provider.address_id)::text = (x36.address_id)::text)) Buffers: shared hit=4 -> Nested Loop Left Join (cost=4.72..11.56 rows=2 width=777) (actual time=0.039..0.039 rows=0 loops=1) Join Filter: (((provider.provider_id)::text = (x35.provider_id)::text) AND ((provider.address_id)::text = (x35.address_id)::text)) Buffers: shared hit=4 -> Hash Right Join (cost=4.17..7.78 rows=2 width=607) (actual time=0.038..0.038 rows=0 loops=1) Hash Cond: (((x62.provider_id)::text = (provider.provider_id)::text) AND ((x62.address_id)::text = (provider.address_id)::text)) Buffers: shared hit=4 -> Append (cost=0.55..3.94 rows=22 width=171) (never executed) -> Index Scan using provider_attribute_sub_0_provider_id_idx on provider_attribute_sub_0 x62 (cost=0.55..3.94 rows=22 width=171) (never executed) Index Cond: ((provider_id)::text = '03563735-3798-441a-aea6-4e561ea347f7'::text) -> Hash (cost=3.59..3.59 rows=2 width=436) (actual time=0.031..0.031 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB Buffers: shared hit=4 -> Index Scan using provider_provider_id_idx on provider (cost=0.55..3.59 rows=2 width=436) (actual time=0.030..0.030 rows=0 loops=1) Index Cond: ((provider_id)::text = '03563735-3798-441a-aea6-4e561ea347f7'::text) Buffers: shared hit=4 -> Materialize (cost=0.56..3.65 rows=4 width=170) (never executed) -> Index Scan using provider_phone_provider_id_idx on provider_phone x35 (cost=0.56..3.62 rows=4 width=170) (never executed) Index Cond: ((provider_id)::text = '03563735-3798-441a-aea6-4e561ea347f7'::text) -> Materialize (cost=0.42..3.44 rows=1 width=89) (never executed) -> Index Scan using provider_language_provider_id_idx on provider_language x36 (cost=0.42..3.44 rows=1 width=89) (never executed) Index Cond: ((provider_id)::text = '03563735-3798-441a-aea6-4e561ea347f7'::text) Planning time: 7195.110 ms Execution time: 0.143 ms Some details around table structure provider_attribute is partitioned tables as below while other tables are normal tables transformations=> \d+ provider_attribute Table "public.provider_attribute" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------------+-------------------+-----------+----------+---------+----------+--------------+------------- paid | character varying | | | | extended | | provider_id | character varying | | not null | | extended | | address_id | character varying | | not null | | extended | | parent_paid | character varying | | | | extended | | attribute_group_id | character varying | | | | extended | | attribute_id | character varying | | not null | | extended | | attribute_value | character varying | | not null | | extended | | Partition key: RANGE (provider_id) Partitions: provider_attribute_sub_0 FOR VALUES FROM ('0') TO ('1'), provider_attribute_sub_1 FOR VALUES FROM ('1') TO ('2'), provider_attribute_sub_2 FOR VALUES FROM ('2') TO ('3'), provider_attribute_sub_3 FOR VALUES FROM ('3') TO ('4'), provider_attribute_sub_4 FOR VALUES FROM ('4') TO ('5'), provider_attribute_sub_5 FOR VALUES FROM ('5') TO ('6'), provider_attribute_sub_6 FOR VALUES FROM ('6') TO ('7'), provider_attribute_sub_7 FOR VALUES FROM ('7') TO ('8'), provider_attribute_sub_8 FOR VALUES FROM ('8') TO ('9'), provider_attribute_sub_9 FOR VALUES FROM ('9') TO ('a'), provider_attribute_sub_a FOR VALUES FROM ('a') TO ('b'), provider_attribute_sub_b FOR VALUES FROM ('b') TO ('c'), provider_attribute_sub_c FOR VALUES FROM ('c') TO ('d'), provider_attribute_sub_d FOR VALUES FROM ('d') TO ('e'), provider_attribute_sub_e FOR VALUES FROM ('e') TO ('f'), provider_attribute_sub_f FOR VALUES FROM ('f') TO ('g') transformations=> \d+ provider_attribute_sub_0 Table "public.provider_attribute_sub_0" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------------+-------------------+-----------+----------+---------+----------+--------------+------------- paid | character varying | | | | extended | | provider_id | character varying | | not null | | extended | | address_id | character varying | | not null | | extended | | parent_paid | character varying | | | | extended | | attribute_group_id | character varying | | | | extended | | attribute_id | character varying | | not null | | extended | | attribute_value | character varying | | not null | | extended | | Partition of: provider_attribute FOR VALUES FROM ('0') TO ('1') Partition constraint: ((provider_id IS NOT NULL) AND ((provider_id)::text >= '0'::character varying) AND ((provider_id)::text < '1'::character varying)) Indexes: "provider_attribute_sub_0_provider_id_idx" btree (provider_id) CLUSTER I have tried to vacuum analyze the whole database still queries are slow in 1 of the environment while faster in another environment. I have also compared and matched all postgres parameters and still no luck in speeding the queries. Any help would be greatly appreciated. Thanks & Regards, Mukesh Chhatani
В списке pgsql-bugs по дате отправления:
Следующее
От: Andres FreundДата:
Сообщение: Re: BUG #16109: Postgres planning time is high across version - 10.6vs 10.10