Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4

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