Query tuning question

Aurora Postgres version 13.7.

Table definition:

acquisition_channel_db=> \d acquisition_channel.acquired_object
Table "acquisition_channel.acquired_object"
Column | Type | Collation | Nullable | Default
----------------------------------------+--------------------------+-----------+----------+-----------------------------------------------------------------
acquired_object_uuid | character varying(36) | | not null |
acquired_object_name | text | | not null |
acquired_object_size | bigint | | |
acquisition_run_record_id | bigint | | |
correlation_id | character varying(36) | | |
acquired_datetime | timestamp with time zone | | |
checksum | character varying(128) | | |
acquisition_method_id | bigint | | |
ol_version | integer | | not null |
created_datetime | timestamp with time zone | | not null |
updated_datetime | timestamp with time zone | | |
status | character varying(50) | | |
parent_acquired_object_uuid | character varying(36) | | |
extracted_from_object_path | text | | |
resource_group_id | bigint | | |
s3_gcs_bucket | character varying(100) | | |
s3_key | character varying(500) | | |
metadata_s3_gcs_bucket | character varying(100) | | |
metadata_s3_key | character varying(500) | | |
routing_result_acquisition_channel_id | bigint | | |
acquired_object_type | character varying(100) | | |
created_by_id | integer | | |
updated_by_id | integer | | |
metadata | jsonb | | |
acquired_object_id | bigint | | not null | nextval('acquisition_channel.acquired_object_id_seq'::regclass)
routed_to_acquisition_channel_datetime | timestamp with time zone | | |
routed_to_acquisition_channel_id | bigint | | |
rendition_guid | character varying(36) | | |
revision_guid | character varying(36) | | |
channel_availability_status | character varying(100) | | |
mime_type | character varying(100) | | |
encryption_public_key_hash | text | | |
acquired_metadata | jsonb | | |
original_acquired_object_name | text | | |
acquired_family_uuid | character varying(36) | | |
last_broadcast_datetime | timestamp with time zone | | |
original_checksum | character varying(128) | | |
Indexes:
"acquired_object_pkey" PRIMARY KEY, btree (acquired_object_uuid)
"acquired_family_uuid_idx" btree (acquired_family_uuid)
"acquired_object_acq_method_id_acq_dt_acquired_object_uuid" btree (acquisition_method_id, acquired_datetime DESC NULLS LAST, acquired_object_uuid)
"acquired_object_acquired_items_initial_ui_page" btree (acquisition_method_id, acquired_datetime DESC NULLS LAST, acquired_object_uuid) WHERE status::text <> 'TEST'::text AND parent_acquired_object_uuid IS NULL
"acquired_object_acquired_object_id_unq" UNIQUE, btree (acquired_object_id)
"acquired_object_acquired_object_name" btree (lower(acquired_object_name))
"acquired_object_acquisition_method_id" btree (acquisition_method_id)
"acquired_object_acquisition_run_record_id" btree (acquisition_run_record_id)
"acquired_object_checksum" btree (checksum)
"acquired_object_correlation_id" btree (correlation_id)
"acquired_object_idx2" btree (parent_acquired_object_uuid, extracted_from_object_path)
"acquired_object_in_channel" UNIQUE, btree (routed_to_acquisition_channel_id, checksum) WHERE routed_to_acquisition_channel_id IS NOT NULL
"acquired_object_routed_to_acq_channel_id_dt_acq_object_uuid" btree (routed_to_acquisition_channel_id, routed_to_acquisition_channel_datetime DESC NULLS LAST, acquired_object_uuid)
"acquired_object_routed_to_acq_channel_id_dt_acq_object_uuid_asc" btree (routed_to_acquisition_channel_id, routed_to_acquisition_channel_datetime, acquired_object_uuid)
"acquired_object_routed_to_acquisition_channel_id" btree (routed_to_acquisition_channel_id)
"acquired_object_trgm_acquired_object_name" gin (lower(acquired_object_name) acquisition_channel.gin_trgm_ops)
Foreign-key constraints:
"acquired_object_acquisition_channel_fk" FOREIGN KEY (routed_to_acquisition_channel_id) REFERENCES acquisition_channel.acquisition_channel(acquisition_channel_id)
"acquired_object_fk2" FOREIGN KEY (acquisition_run_record_id) REFERENCES acquisition_channel.acquisition_run_record(acquisition_run_record_id)
"acquired_object_fk3" FOREIGN KEY (acquisition_method_id) REFERENCES acquisition_channel.acquisition_method(acquisition_method_id)
"acquired_object_fk4" FOREIGN KEY (parent_acquired_object_uuid) REFERENCES acquisition_channel.acquired_object(acquired_object_uuid)
"acquired_object_fk5" FOREIGN KEY (routing_result_acquisition_channel_id) REFERENCES acquisition_channel.acquisition_channel(acquisition_channel_id)
Referenced by:
TABLE "acquisition_channel.acquired_object" CONSTRAINT "acquired_object_fk4" FOREIGN KEY (parent_acquired_object_uuid) REFERENCES acquisition_channel.acquired_object(acquired_object_uuid)
TABLE "acquisition_channel.broadcast_item" CONSTRAINT "broadcast_item_acquired_object_fk" FOREIGN KEY (acquired_object_uuid) REFERENCES acquisition_channel.acquired_object(acquired_object_uuid)
TABLE "acquisition_channel.routing_record" CONSTRAINT "routing_record_acquired_object_fk" FOREIGN KEY (acquired_object_uuid) REFERENCES acquisition_channel.acquired_object(acquired_object_uuid)

Extension pg_trgm is installed at version 1.3 and index acquired_object_trgm_acquired_object_name is a gin index.

Query and plan:

acquisition_channel_db=> explain (analyze, buffers)
acquisition_channel_db-> select *
acquisition_channel_db-> from acquisition_channel.acquired_object acquiredob0_
acquisition_channel_db-> where lower(acquiredob0_.acquired_object_name) like lower('%de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%') limit 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=46341.49..50488.39 rows=100 width=1342) (actual time=7589.846..7593.992 rows=1 loops=1)
Buffers: shared hit=638206 read=1127
I/O Timings: read=932.828
-> Gather (cost=46341.49..3436021.28 rows=81740 width=1342) (actual time=7589.845..7593.990 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=638206 read=1127
I/O Timings: read=932.828
-> Parallel Bitmap Heap Scan on acquired_object acquiredob0_ (cost=45341.49..3426847.28 rows=34058 width=1342) (actual time=7584.021..7584.022 rows=0 loops=3)
Recheck Cond: (lower(acquired_object_name) ~~ '%de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::text)
Heap Blocks: exact=1
Buffers: shared hit=638206 read=1127
I/O Timings: read=932.828
-> Bitmap Index Scan on acquired_object_trgm_acquired_object_name (cost=0.00..45321.05 rows=81740 width=0) (actual time=7589.148..7589.149 rows=1 loops=1)
Index Cond: (lower(acquired_object_name) ~~ '%de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::text)
Buffers: shared hit=638205 read=1127
I/O Timings: read=932.828
Planning:
Buffers: shared hit=7
Planning Time: 0.209 ms
Execution Time: 7594.346 ms

It is using the GIN index but it did 639k total buffer reads to find 1 row which seems like a ton. I don't know how GIN/GIN_TRGM_OPS are implemented but does 638k buffer reads to find 1 row using that type of index seem high or does that seem as expected?

I noticed for this 1 row returned the acquired_object_name starts with de_ so the leading % isn't really needed for this data point . There is a different index that is a normal btree index that could be used by this query if the leading % was removed:

acquired_object_acquired_object_name btree (lower(acquired_object_name))

Here is the plan if I remove the leading %:

acquisition_channel_db=> explain (analyze, buffers)
acquisition_channel_db-> select *
acquisition_channel_db-> from acquisition_channel.acquired_object acquiredob0_
acquisition_channel_db-> where lower(acquiredob0_.acquired_object_name) like lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%') limit 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=48145.49..52292.39 rows=100 width=1342) (actual time=8639.428..8643.529 rows=1 loops=1)
Buffers: shared hit=827759 read=143
I/O Timings: read=119.373
-> Gather (cost=48145.49..3437825.28 rows=81740 width=1342) (actual time=8639.427..8643.527 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=827759 read=143
I/O Timings: read=119.373
-> Parallel Bitmap Heap Scan on acquired_object acquiredob0_ (cost=47145.49..3428651.28 rows=34058 width=1342) (actual time=8633.200..8633.201 rows=0 loops=3)
Recheck Cond: (lower(acquired_object_name) ~~ 'de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::text)
Heap Blocks: exact=1
Buffers: shared hit=827759 read=143
I/O Timings: read=119.373
-> Bitmap Index Scan on acquired_object_trgm_acquired_object_name (cost=0.00..47125.05 rows=81740 width=0) (actual time=8638.786..8638.787 rows=1 loops=1)
Index Cond: (lower(acquired_object_name) ~~ 'de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::text)
Buffers: shared hit=827758 read=143
I/O Timings: read=119.373
Planning:
Buffers: shared hit=7
Planning Time: 0.226 ms
Execution Time: 8643.664 ms

I was surprised the optimizer decided to still use the gin index vs the acquired_object_acquired_object_name index. And now the query did 827k buffer reads to find that 1 row via the index. I tried using pg_hint_plan to get the query to use the acquired_object_acquired_object_name index but the optimizer still wouldn't use it. So then I decided to disable bitmap scan to see if it would then use this other index (this time I'm just doing an explain to see the plan):

acquisition_channel_db=> set enable_bitmapscan to off;
SET
acquisition_channel_db=> explain
acquisition_channel_db-> select *
acquisition_channel_db-> from acquisition_channel.acquired_object acquiredob0_
acquisition_channel_db-> where lower(acquiredob0_.acquired_object_name) like lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%') limit 100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Limit (cost=1000.00..122287.58 rows=100 width=1342)
-> Gather (cost=1000.00..99141466.60 rows=81740 width=1342)
Workers Planned: 2
-> Parallel Seq Scan on acquired_object acquiredob0_ (cost=0.00..99132292.60 rows=34058 width=1342)
Filter: (lower(acquired_object_name) ~~ 'de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::text)

Wow, so rather than using the acquired_object_acquired_object_name it thinks a full scan of the table would be better.... The table is 718 GB. It didn't even want to use that gin index- when accessing a gin index can that only be done via a bitmap index scan? Something seems out of whack here but I don't know what it is. Leaving enable_bitmapscan to off I also tried disabling seqscan- the plan below shows it will still to a seq scan and note the cost now.

acquisition_channel_db=> set enable_seqscan to off;
SET
acquisition_channel_db=> explain
acquisition_channel_db-> select *
acquisition_channel_db-> from acquisition_channel.acquired_object acquiredob0_
acquisition_channel_db-> where lower(acquiredob0_.acquired_object_name) like lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%') limit 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=10000000000.00..10000130032.86 rows=100 width=1342)
-> Seq Scan on acquired_object acquiredob0_ (cost=10000000000.00..10106288859.64 rows=81740 width=1342)
Filter: (lower(acquired_object_name) ~~ 'de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::text)

My questions:

1. Does 638k buffer reads using the gin index to find 1 row seem appropriate? That seems like a ton of work to me.
2. When I remove the leading % it still uses the gin index but now it does 827k buffer reads to find 1 row. Why does it still want to use this gin index vs using the other index I note above and why is it doing even more work when removing the leading %?
3. If I disable bitmap scan it then prefers to full scan the table vs using the other index I note above. Why would it still not want to use this other index?
4. If I also disable seqscan it still won't use this other index. Why?

I don't know what other info would be helpful here but I'll show settings related to Query Tuning / Planner Method Configuration (after re-logging in to show current settings) along with table/index statistics. I assume I'm overlooking something rather simple here but I'm not seeing it. Any input would be much appreciated.

Regards
Steve

acquisition_channel_db=> select name, setting from pg_settings where category = 'Query Tuning / Planner Method Configuration';
name | setting
-----------------------------------------+---------
apg_enable_correlated_any_transform | off
apg_enable_function_migration | on
apg_enable_not_in_transform | on
apg_enable_remove_redundant_inner_joins | off
apg_enable_semijoin_push_down | off
apg_force_full_key_semijoin | off
apg_force_semijoin_push_down | off
apg_force_single_key_semijoin | off
enable_bitmapscan | on
enable_gathermerge | on
enable_hashagg | on
enable_hashjoin | on
enable_incremental_sort | on
enable_indexonlyscan | on
enable_indexscan | on
enable_material | on
enable_mergejoin | on
enable_nestloop | on
enable_parallel_append | on
enable_parallel_hash | on
enable_partition_pruning | on
enable_partitionwise_aggregate | off
enable_partitionwise_join | off
enable_seqscan | on
enable_sort | on
enable_tidscan | on
(26 rows)

acquisition_channel_db=> \x on
Expanded display is on.

acquisition_channel_db=> select * FROM pg_stat_user_tables where relname = 'acquired_object';
-[ RECORD 1 ]-------+------------------------------
relid | 16777
schemaname | acquisition_channel
relname | acquired_object
seq_scan | 1218
seq_tup_read | 307573542582
idx_scan | 2260152042
idx_tup_fetch | 94409160669
n_tup_ins | 140420690
n_tup_upd | 642849845
n_tup_del | 0
n_tup_hot_upd | 251641126
n_live_tup | 821351776
n_dead_tup | 26646304
n_mod_since_analyze | 18564560
n_ins_since_vacuum | 7485136
last_vacuum |
last_autovacuum | 2024-08-17 15:39:04.259598+00
last_analyze |
last_autoanalyze | 2024-08-20 06:46:16.897584+00
vacuum_count | 0
autovacuum_count | 6
analyze_count | 0
autoanalyze_count | 21

acquisition_channel_db=> select * from pg_stats where tablename = 'acquired_object' and attname = 'acquired_object_name';
-[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | acquisition_channel
tablename | acquired_object
attname | acquired_object_name
inherited | f
null_frac | 0
avg_width | 43
n_distinct | 3.053536e+06
most_common_vals | {.xxxxxxxx.xxxxxx,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.pdf,yyyyyyyyyyyyyyyyyyyyyyyyyyyy.pdf} <--- replace actual values due to possible concerns with values shown
most_common_freqs | {0.00026666667,0.00016666666,0.00016666666}
histogram_bounds | {removing values also due to possible concerns with values shown}
correlation | 0.1324247
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |

This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html