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
On Mon, Mar 11, 2024 at 12:00 PM hassan rafi <haassaan.kh...@gmail.com>
wrote:
> Thanks all. Will try upgrading the postgres version.
>
> On Sun, Mar 10, 2024 at 11:44 PM Ron Johnson <ronljohnso...@gmail.com>
> wrote:
>
>> On Sun, Mar 10, 2024 at 1:34 PM Greg Sabino Mullane <htamf...@gmail.com>
>> wrote:
>>
>>>
>>> On Sat, Mar 9, 2024 at 1:57 PM hassan rafi <haassaan.kh...@gmail.com>
>>> wrote:
>>>
>>>> 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.
>>
>>
