RE: [EXT] Re: Query performance going from Oracle to Postgres - Mailing list pgsql-general

Thanks to all who replied on this issue.  I overlooked the difference in the execution plan between Oracle and
Postgres. Oracle in fact does an index range scan only with no table lookup.  When I changed the query to be a count(*)
Postgreswent from 332 logical reads to 19.  Oracle did 20.  Oracle did 20 regardless if it was count(*) or
count(non-indexed-column).

Regards
Steve

-----Original Message-----
From: Peter Geoghegan <pg@bowt.ie> 
Sent: Thursday, September 7, 2023 8:22 PM
To: David Rowley <dgrowleyml@gmail.com>
Cc: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; pgsql-general@lists.postgresql.org; Wong, Kam Fook (TR
Technology)<kamfook.wong@thomsonreuters.com>
 
Subject: [EXT] Re: Query performance going from Oracle to Postgres

External Email: Use caution with links and attachments.

On Thu, Sep 7, 2023 at 3:48 AM David Rowley <dgrowleyml@gmail.com> wrote:
> On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan <pg@bowt.ie> wrote:
> > It seems likely that the problem here is that some of the predicates 
> > appear as so-called "Filter:" conditions, as opposed to true index 
> > quals.
>
> hmm, if that were true we'd see "Rows Removed by Filter" in the 
> explain analyze.

That's why I hedged, with "seems likely". The problem with using filter conditions rather than true index quals isn't
limitedto the problem of extra heap accesses. It happened to be convenient to make my point that way, but that isn't
particularlyfundamental here.
 

I deliberately chose to make my example involve an index-only scan (that doesn't require any heap accesses) for this
reason.

> I think all that's going on is that each tuple is on a different page 
> and the heap accesses are just causing many buffers to be accessed.

This index is an absolute monstrosity. I find it far easier to believe that the real explanation is the one that Steve
intuited:that there is an issue with the way that the physical data structures (which are more or less comparable in
bothsystems) are accessed in Postgres.
 

The index in question
("history_event_display_timesta_prism_guid_display_timestamp_idx1")
has certain columns that are omitted from the query. These columns nevertheless appear between other columns that the
queryfilters on.
 
The leading two columns ("prism_guid" and "display_timestamp") are made into index quals by Postgres, but the next
indexcolumn after those two ("unique_lookup_key") is omitted by the query, and so isn't an index qual. In fact *four*
columnsare omitted after that one. But, the very-low-order index column "product_sid" *does* appear in the query, and
soalso appears as Postgres index quals.
 

There is every chance that the true underlying explanation is that Oracle is able to skip over significant parts of the
indexstructure dynamically. In fact I'm practically certain that that's the case, since the "product_sid" column
appearsas an "access predicate", rather than as a "filter predicate". These terms are explained here:
 


https://urldefense.com/v3/__https://use-the-index-luke.com/sql/explain-plan/oracle/filter-predicates__;!!GFN0sa3rsbfR8OLyAw!fZJXNap0mP7xLTcE_5unwlR5eDfBHgw2F5LTBDGtjV_btV6Zze0MSWnTHamU16Fmu-kII-FwrC4WK7WLP60$



https://urldefense.com/v3/__https://use-the-index-luke.com/sql/explain-plan/postgresql/filter-predicates__;!!GFN0sa3rsbfR8OLyAw!fZJXNap0mP7xLTcE_5unwlR5eDfBHgw2F5LTBDGtjV_btV6Zze0MSWnTHamU16Fmu-kII-FwrC4WCXw7ubs$


How could "product_sid" be used as an "access predicate" given the omitted index columns? It seems very likely that
partsof the index can be skipped in Oracle, but not in Postgres -- at least not yet.
 

Like Markus Winand, I think that it's a real problem that EXPLAIN doesn't yet expose the difference between access
predicatesand filter predicates. Some of the index quals shown by EXPLAIN are marked SK_BT_REQFWD/SK_BT_REQBKWD by
nbtree(meaning that they're what Oracle calls "access predicates"), while other are not (meaning that they're what
Oraclecalls "filter predicates"). That issue makes it far harder to spot these sorts of problems.
 

> It seems to me that an IOS would likely fix that problem which is why 
> I suggested count(*) instead of
> count(<not_null_column_thats_the_only_col_used_thats_not_in_the_index>
> )

Probably, but why should we have to use an index-only scan? And what about my example, that already used one, and still
showeda big disparity where there is no inherently reason why there had to be? My example didn't involve jumping to
anotherpart of the index because the problem seems to be more general than that.
 

--
Peter Geoghegan