We have migrated to postgres version 16.1, but still due to very high update activity on our DB, we are seeing elevated response times, though now the planning time is less.
catalog-v2=> explain (analyze, verbose, settings, buffers) SELECT products_inventory_delta.upc FROM products_inventory_delta WHERE products_inventory_delta.modality = 'pickup' AND products_inventory_delta.store_id = '70600372' ORDER BY upc DESC LIMIT 51 OFFSET 30000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1450.68..1450.73 rows=1 width=14) (actual time=5049.115..5049.116 rows=0 loops=1)
Output: upc
Buffers: shared hit=33359 read=6590 dirtied=9379
-> Index Only Scan Backward using products_inventory_delta_pkey on public.products_inventory_delta (cost=0.57..1450.68 rows=28606 width=14) (actual time=1.056..5047.472 rows=28299 loops=1)
Output: upc
Index Cond: ((products_inventory_delta.store_id = '70600372'::text) AND (products_inventory_delta.modality = 'pickup'::modality))
Heap Fetches: 16840
Buffers: shared hit=33359 read=6590 dirtied=9379
Settings: effective_cache_size = '192GB', jit = 'off', random_page_cost = '2', work_mem = '2097151kB'
Query Identifier: 220372279818787780
Planning Time: 0.062 ms
Execution Time: 5049.131 ms
Thanks,
Hassan
Thanks all. Will try upgrading the postgres version.
Would upgrading to the latest version of Postgres potentially solve the issue?
Potentially, yes, but the only one who can answer that for sure is you. Upgrade to 11.22 and re-run the query. Worst case scenario, it runs the same speed but you gained yourself a bunch of bugfixes and CVE resolutions. If the problem persists on 11.22, spin up a Postgres 16, load the data, and test it there.
We have a similar situation with 9.6.24. 14.10 is noticeably faster (between 10% and 80%, depending on the query.