Re: Query performance going from Oracle to Postgres

On Wed, Sep 6, 2023 at 1:07 PM Dirschel, Steve
<steve(dot)dirschel(at)thomsonreuters(dot)com> wrote:
> Oracle will find the same 332 rows using the same index but in Oracle it only does 20 logical reads. I thought maybe the index was fragmented so I reindexed that index:

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. For reasons that aren't particularly fundamental, Postgres
B-Trees cannot push down != (or <>) to the index level. Strangely
enough, this is possible with IS NOT NULL. I'm working to improve
things in this area.

That difference is easy to see in the following example. The first
query does far fewer buffer accesses than the. second query, despite
being very similar:

regression=# explain (analyze, buffers) select * from multi_test where
a = 1 and b is not null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using multi_test_idx on multi_test (cost=0.29..50.10
rows=2191 width=8) (actual time=0.095..0.100 rows=14 loops=1)
Index Cond: ((a = 1) AND (b IS NOT NULL))
Heap Fetches: 0
Buffers: shared hit=3
Planning Time: 0.056 ms
Execution Time: 0.145 ms
(6 rows)

regression=# explain (analyze, buffers) select * from multi_test where
a = 1 and b != 42;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using multi_test_idx on multi_test
(cost=0.29..222.57 rows=2191 width=8) (actual time=0.087..2.982
rows=14 loops=1)
Index Cond: (a = 1)
Filter: (b <> 42)
Rows Removed by Filter: 10000
Heap Fetches: 0
Buffers: shared hit=11
Planning Time: 0.076 ms
Execution Time: 3.204 ms
(8 rows)

(There are lots of index tuples matching (a, b) = "(1, NULL)" here, you see.)

--
Peter Geoghegan