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(*) Postgres went 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@xxxxxxx>
Sent: Thursday, September 7, 2023 8:22 PM
To: David Rowley <dgrowleyml@xxxxxxxxx>
Cc: Dirschel, Steve <steve.dirschel@xxxxxxxxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx; Wong, Kam Fook (TR Technology) <kamfook.wong@xxxxxxxxxxxxxxxxxx>
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@xxxxxxxxx> wrote:
> On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan <pg@xxxxxxx> 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 limited to the problem of extra heap accesses. It happened to be convenient to make my point that way, but that isn't particularly fundamental 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 both systems) 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 query filters on.
The leading two columns ("prism_guid" and "display_timestamp") are made into index quals by Postgres, but the next index column after those two ("unique_lookup_key") is omitted by the query, and so isn't an index qual. In fact *four* columns are omitted after that one. But, the very-low-order index column "product_sid" *does* appear in the query, and so also 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 index structure dynamically. In fact I'm practically certain that that's the case, since the "product_sid" column appears as 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 parts of 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 predicates and 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 Oracle calls "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 showed a big disparity where there is no inherently reason why there had to be? My example didn't involve jumping to another part of the index because the problem seems to be more general than that.
--
Peter Geoghegan
