Re: Query performance going from Oracle to Postgres

On Wed, 2023-09-06 at 20:06 +0000, Dirschel, Steve wrote:
> We are in the process of converting from Oracle to Postgres and I have a query that is using
> the same index in Postgres as is used in Oracle but in Postgres the query does 16x more
> buffer/logical reads.  I’d like to understand why.
>  
> explain (analyze, buffers)
> select count(historyeve0_.HISTORY_EVENT_SID) as col_0_0_
> from hist28.history_event_display_timestamp_20230301 historyeve0_ where historyeve0_.IS_DELETED=0
> and historyeve0_.PRISM_GUID='i0accd6a20000018405f095ee669dc5b4'
> and historyeve0_.PRODUCT_SID='CARSWELL.WESTLAW'
> and (historyeve0_.EVENT_TYPE not in 
>
('versionsSearchWithinQueryEvent','notesOfDecisionsSearchWithinQueryEvent','citingReferencesSearchWithinQueryEvent','tocSearchWithinQueryEvent','searchWithinDocumentEvent','coCitesSearchWithinQueryE
> vent'))
> and (historyeve0_.PRODUCT_VIEW in ('DefaultProductView','TNPPlus','PLCUS','Indigo','IndigoPremium','INDIGOCA')
> or historyeve0_.PRODUCT_VIEW is null)
> and historyeve0_.CLIENT_ID='WLNCE_VNJXL7'
> and (historyeve0_.DISPLAY_TIMESTAMP between '2022-03-01 00:00:00' and '2023-03-01 23:59:59.999');
>  
> Aggregate  (cost=56.64..56.65 rows=1 width=8) (actual time=0.930..0.931 rows=1 loops=1)
>    Buffers:shared hit=341
>    ->  Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx1 on
history_event_display_timestamp_20230301historyeve0_  (cost=0.42..56.64 rows=1 width=33) (actual 
> time=0.034..0.897 rows=332 loops=1)
>          Index Cond: (((prism_guid)::text = 'i0accd6a20000018405f095ee669dc5b4'::text) AND (display_timestamp >=
'2022-03-0100:00:00'::timestamp without time zone) AND (display_timestamp <= '2023- 
> 03-01 23:59:59.999'::timestamp without time zone) AND ((product_sid)::text = 'CARSWELL.WESTLAW'::text) AND
(is_deleted= '0'::numeric) AND ((client_id)::text = 'WLNCE_VNJXL7'::text)) 
>          Filter: (((event_type)::text <> ALL
>
('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent,coCitesSearchWithinQueryEvent}'::t
> ext[])) AND (((product_view)::text = ANY
('{DefaultProductView,TNPPlus,PLCUS,Indigo,IndigoPremium,INDIGOCA}'::text[]))OR (product_view IS NULL))) 
>          Buffers: shared hit=341
> Planning:
>    Buffers: shared hit=6
> Planning Time: 0.266 ms
> Execution Time: 0.965 ms
> (10 rows)
>  
> *** 341 logical reads to find 332 rows.
>  
> Plan hash value: 3361538278
>  
> ----------------------------------------------------------------------------------------------------------
> | Id  | Operation                | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
> ----------------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT         |                     |      1 |        |      1 |00:00:00.01 |     20 |
> |   1 |  SORT AGGREGATE          |                     |      1 |      1 |      1 |00:00:00.01 |      20 |
> |*  2 |   FILTER                 |                     |      1 |        |   332 |00:00:00.01 |      20 |
> |   3 |    PARTITION RANGE SINGLE|                     |      1 |      1 |    332 |00:00:00.01 |      20 |
> |*  4 |     INDEX RANGE SCAN     |HISTORY_EVENT_IDX02 |      1 |      1 |    332 |00:00:00.01 |      20 |
> ----------------------------------------------------------------------------------------------------------
>  
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>  
>    2 - filter(TO_TIMESTAMP(:SYS_B_16,:SYS_B_17)<=TO_TIMESTAMP(:SYS_B_18,:SYS_B_19))
>    4 - access("HISTORYEVE0_"."PRISM_GUID"=:SYS_B_01 AND
>               "HISTORYEVE0_"."DISPLAY_TIMESTAMP">=TO_TIMESTAMP(:SYS_B_16,:SYS_B_17) AND
>               "HISTORYEVE0_"."PRODUCT_SID"=:SYS_B_02 AND "HISTORYEVE0_"."IS_DELETED"=:SYS_B_00 AND
>               "HISTORYEVE0_"."CLIENT_ID"=:SYS_B_15 AND "HISTORYEVE0_"."DISPLAY_TIMESTAMP"<=TO_TIMESTAMP(:SYS_B_1
>               8,:SYS_B_19))
>        filter(("HISTORYEVE0_"."CLIENT_ID"=:SYS_B_15 AND "HISTORYEVE0_"."PRODUCT_SID"=:SYS_B_02
>               AND "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_03 AND "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_04 AND
>               "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_05 AND "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_06 AND
>               "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_07 AND "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_08 AND
>               "HISTORYEVE0_"."IS_DELETED"=:SYS_B_00 AND (INTERNAL_FUNCTION("HISTORYEVE0_"."PRODUCT_VIEW") OR
>               "HISTORYEVE0_"."PRODUCT_VIEW" IS NULL)))
>  
> It finds the same 332 rows but it does only 20 logical reads.

In PostgreSQL, the 332 matching rows seem to me stored mostly on different pages, while they are
better clustered in your Oracle table.

If it is really very important for you, and the 57 milliseconds for the index scan is too much,
you can reorganize the table with

 CLUSTER hist28.history_event_display_timestamp_20230301 USING
history_event_display_timesta_prism_guid_display_timestamp_idx1;

That should reduce the number of pages read.

Yours,
Laurenz Albe