From: | Michael Guissine <mguissine(at)gmail(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4 |
Date: | 2022-10-19 19:32:17 |
Message-ID: | CACxDrAm6x9MWvkz14_7D_uWcoAtyx7WaMOUmvv1fEayM+Cbe3w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Guissine | 2022-10-19 19:44:03 | Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4 |
Previous Message | Jeff Janes | 2022-10-19 19:00:59 | Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4 |