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

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(at)anarazel(dot)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
>