Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4 - Mailing list pgsql-bugs
| From | Michael Guissine |
|---|---|
| Subject | Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4 |
| Date | |
| Msg-id | CACxDrAm6x9MWvkz14_7D_uWcoAtyx7WaMOUmvv1fEayM+Cbe3w@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4 (Andres Freund <andres@anarazel.de>) |
| Responses |
Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4
|
| List | pgsql-bugs |
Thank you Andres,
The column names in the plan and queries are obfuscated, the cold cache should not be an issue as we upgraded 3 days ago and since reindexed/re-analyzed everything plus we have an active application running against this database so the data should be cached
here is a obfuscated table definition, there are multiple indexes on those columns to support different query patterns, note that the partitioned_table_partition_key_start_time_text index was added after the upgrade as we thought it might help the optimizer to come up with a better plan but it didn't help
Partitioned table "public.partitioned_table"
Column | Type | Collation | Nullable | Default
---------------------------+-----------------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('partitioned_table_id_seq'::regclass)
partition_key | integer | | not null |
text | text | | |
description | text | | |
...
more fields
...
Partition key: HASH (partition_key)
Indexes:
"partitioned_table_pkey" PRIMARY KEY, btree (partition_key, id)
"partitioned_table_partition_key_start_time_index" btree (partition_key, start_time)
"partitioned_table_partition_key_start_time_text" btree (partition_key, start_time, text)
"partitioned_table_partition_key_text_idx" gin (partition_key, text gin_trgm_ops)
"partitioned_table_text_tsv_index" gin (to_tsvector('simple'::regconfig, text)) WHERE text IS NOT NULL
Number of partitions: 128 (Use \d+ to list them.)
On Wed, Oct 19, 2022 at 2:46 PM Andres Freund <andres@anarazel.de> wrote:
Yi,
On 2022-10-19 16:11:15 +0000, PG Bug reporting form wrote:
> We recently upgraded our AWS RDS instances to PG 14.4 and are seeing weird
> performance degradation.
Are they actually configured the same? Of particular interest would be
shared_buffers, effective_cache_size, random_page_cost, seq_page_cost,
cpu_index_tuple_cost, cpu_operator_cost, cpu_tuple_cost, work_mem.
Your explain shows that most of the time is spent doing IO:
Append (cost=0.43..93144.17 rows=121 width=1121) (actual time=225005.053..1160202.658 rows=7 loops=1)
Buffers: shared hit=379846 read=2382552 dirtied=57320 written=231982
I/O Timings: read=1113819.977 write=2850.752
Is there a chance at least part of this is due to a cold cache after
upgrading?
What is the definition of the indexes the partitions?
Based on the names of the indexes I'd guess that the reason the bitmap scans
are bad is that the index includes the partition_key column as a leading
column, but skips over that column and just filters by "text".
I'm a bit confused by your plan showing:
Rows Removed by Index Recheck: 62841
and
Heap Blocks: exact=46111
in the same node.
I haven't rechecked the code, but I don't think that should happen for btree
indexes. It could however for gin, bloom, hash etc.
> Query and query plan are attached below. We also tried to turn off
> enable_bitmapscan and observed that query is performing well which was our
> experience in the previous version (pg 13.4)
>
> https://explain.depesz.com/s/F587
Do you know what the plan was in 13?
Greetings,
Andres Freund
pgsql-bugs by date: