설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은
결과보기
EnterpriseDB
총 게시물 185건
목록 글쓰기 다음글

Negative Predicates Can Cause Excessive Heap Access

글쓴이 : 모델광   날짜 : 2026-11-18 (수) 20:54   조회 : 6 인쇄

Now I am working on a project where I migrate a database from Oracle to PostgreSQL. I found an SQL which runs much slower in PostgreSQL than in Oracle. Although PostgreSQL produced a similar execution plan, the performance was not as good as Oracle's. In the article, I will show you how negative predicates in PostgreSQL can cause excessive heap access and how to work around it.

Let's start by creating a test table called online_order:


-- tested on PostgreSQL 18
-- Create and populate Online Orders
DROP TABLE IF EXISTS online_order CASCADE;
CREATE TABLE online_order (
    ord_no        bigint       NOT NULL,
    cust_no       int          NOT NULL,
    ord_date      date         NOT NULL,
    ord_dt        varchar(8)   NOT NULL,
    ord_status_cd varchar(1)   NOT NULL,
    comment       varchar(100),
    CONSTRAINT online_order_pk PRIMARY KEY (ord_no)
);

INSERT INTO online_order
SELECT 
    i AS ord_no,
    MOD(i, 1000000) AS cust_no,
    '2022-01-20'::date + ((i - 1) / 2000)::int AS ord_date,
    TO_CHAR('2022-01-20'::date + ((i - 1) / 2000)::int, 'YYYYMMDD') AS ord_dt,
    (MOD(i, 4) + 1)::varchar(1) AS ord_status_cd,
    LPAD('x', 100, 'x') AS comment
FROM generate_series(1, 2000000, 2) AS a(i);

-- Create an index
create index online_order_x02 on online_order(cust_no);

Consider the following query:
​
SELECT *
FROM online_order
WHERE cust_no BETWEEN 10000 AND 90000
  AND NOT (cust_no BETWEEN 20000 AND 50000);

-- Here is the plan generated by the optimizer.
Index Scan using online_order_x02 on online_order (actual time=0.094..47.961 rows=50000.00 loops=1)
  Index Cond: ((cust_no >= 10000) AND (cust_no <= 90000))
  Filter: ((cust_no < 20000) OR (cust_no > 50000))
  Rows Removed by Filter: 30000
  Index Searches: 1
  Buffers: shared hit=80200
Planning:
  Buffers: shared hit=36
Planning Time: 0.267 ms
Execution Time: 51.203 ms

Note that PostgreSQL used a regular Index Scan and applied the exculsion predicate ( NOT (cust_no between ...)) as a filter fetching table blocks. As a result, it accessed far more buffers than necessary.

If you do the same experiment on Oracle 12c, you will notice that Oracle evaluates the exclusion predicate using index entries before accessing table blocks, substantially reducing table access.

Let's investigate why the PostgreSQL execution plan behaves this way, how to interpret the difference, and which SQL rewrites can help reduce unnecessay heap acess.

First, consider the query without the exclusion condition:


select *
  from online_order 
 where cust_no between 10000 and 90000;

Index Scan using online_order_x02 on online_order (actual time=0.015..38.546 rows=80000.00 loops=1)
  Index Cond: ((cust_no >= 10000) AND (cust_no <= 90000))
  Index Searches: 1
  Buffers: shared hit=80200
Planning:
  Buffers: shared hit=4
Planning Time: 0.151 ms
Execution Time: 43.379 ms

The range predicate appears inuder Index Cond.
The plan reports approximately 80,200 shared buffer hits to return 80,000 rows.

Notice the differences compred with the SQL with the exclusion predicate:

 - Index Cond of the SQL with the exclusion condition (NOT (cust_no between ...)) contains only the cust_no between 10000 and 90000 condition.
 - The exclusion condition appears under Filer.
 - PostgreSQL fetches rows in the table blocks for the broader range, then rejects 30,000 rows.
 - The buffer hit count remains about 80,200, even though only 50,000 rows are returned.

The exclusion condition is logically equivalent to:

  cust_no < 20000 OR cust_no > 50000

However, PostgreSQL did not use this condition to narrow the entries visited by the regular Index Scan in the execution plan. The exclusion predicate was evaluated after a heap tuple had been fetched.

The issue is not that PostgreSQL can never evaluate a negative predicate through an index. The issue is that a regular Index Scan may fetch heap tuples before applying a predicate that could otherwise help reduce table block access.

The same general concern can arise with several types of predicates. However, the exact execution plan depends on the expression and available index defintions.

 - Not-equal predicates
   where cust_no <> 30000
 - Exclusion predicates
   WHERE NOT (cust_no BETWEEN 20000 AND 50000)
 - Functions applied to indexed columns
   WHERE TRIM(cust_no) = '30000'
 - Substring and suffix searches
   WHERE cust_no LIKE '%0022'

Workaround 1: Rewrite the Predicate as UNION ALL
One practical workaround is to divide the original range into two disjoint ranges.
The condition (not (cust_no >= 20000 and cust_no <= 50000))
is identical with (cust_no < 20000 or cust_no > 50000).

Therefore the original query can be rewritten:
select *
  from online_order 
 where (cust_no >= 10000 and cust_no <= 90000 and cust_no < 20000)
 union all
 select *
  from online_order 
 where (cust_no >= 10000 and cust_no <= 90000 and cust_no > 50000);

Append (actual time=0.018..27.267 rows=50000.00 loops=1)
  Buffers: shared hit=50129
  ->  Index Scan using online_order_x02 on online_order (actual time=0.018..4.549 rows=10000.00 loops=1)
        Index Cond: ((cust_no >= 10000) AND (cust_no <= 90000) AND (cust_no < 20000))
        Index Searches: 1
        Buffers: shared hit=10028
  ->  Index Scan using online_order_x02 on online_order online_order_1 (actual time=0.024..17.062 rows=40000.00 loops=1)
        Index Cond: ((cust_no >= 10000) AND (cust_no <= 90000) AND (cust_no > 50000))
        Index Searches: 1
        Buffers: shared hit=40101
Planning:
  Buffers: shared hit=8
Planning Time: 0.283 ms
Execution Time: 30.117 ms

The two branches do not overlap, so UNION ALL preserves the original result without requiring duplicate elimination. The rewritten query reduced buffer hits from 80,200 to 50,129 and excution time from 51.2 ms to 30.1 ms.

This rewrite is worth testing when the excustion condition can be split into a small number of disjoint, indexable ranges.

Workaround 2: Encouraging a Bitmap Scan
Another approach is to use a Bitmap Heap Scan.
In this experiment, I disabled regular Index Scan:

SET enable_indexscan TO off;

Then I exeucted the original query again. Here is the execution plan produced by PostgreSQL:


Bitmap Heap Scan on online_order (actual time=2.818..11.391 rows=50000.00 loops=1)
  Recheck Cond: (((cust_no < 20000) AND (cust_no >= 10000) AND (cust_no <= 90000)) OR ((cust_no > 50000) AND (cust_no >= 10000) AND (cust_no <= 90000)))
  Heap Blocks: exact=965
  Buffers: shared hit=1094
  ->  BitmapOr (actual time=2.617..2.618 rows=0.00 loops=1)
        Buffers: shared hit=129
        ->  Bitmap Index Scan on online_order_x02 (actual time=0.500..0.500 rows=10000 loops=1)
              Index Cond: ((cust_no < 20000) AND (cust_no >= 10000) AND (cust_no <= 90000))
              Index Searches: 1
              Buffers: shared hit=28
        ->  Bitmap Index Scan on online_order_x02 (actual time=2.116..2.116 rows=40000 loops=1)
              Index Cond: ((cust_no > 50000) AND (cust_no >= 10000) AND (cust_no <= 90000))
              Index Searches: 1
              Buffers: shared hit=101
Planning:
  Buffers: shared hit=4
Planning Time: 0.265 ms
Execution Time: 14.471 ms

The buffer hit count fell from 80,200 to 1,094, and the elapsed time dropped from 51.2 ms to 14.5 ms.

The Bitmap Index Scans identify matching tuple locations. BitmapOr combines the two sets of tuple locations, and the Bitmap Heap Scan visits the heap blocks containing the qualifying rows.

Because many qualifying rows are located on the same heap page, PostgreSQL can access those pages more efficiently than it can when visiting heap tuples individually through a regular Index Scan.

This is especially beneficial when the qualifying rows occupy a relatively small number of heap pages.

One small detail in the execution plan may appear confusing:

BitmapOr (actual time=2.617..2.618 rows=0.00 loops=1)

The rows=0.00 value does not mean that BitmapOr found zero matching rows. 

PostgreSQL manual says that BitmapAnd and BitmapOr nodes always report their actual row counts as zero, due to implementation limitations.
PostgreSQL: Documentation: 18: 14.1. Using EXPLAIN

Practical Recommendations
When a PostgreSQL query uses an indexed range condition combined with an exclusion predicate, I recommend the following tuning process.
1. Inspect Index Cond and Filter. Determine whether the exclusion condition is being used to narrow index access or is being evaluated after heap retrieval.
2. Check the number of rows removed by the filter. A large value can indicate that many heap tuples are fetched only to be discarded.
3. Rewrite disjoint ranges using UNION ALL. This can allow each branch to use a more selective index range.
4. Test a Bitmap Heap Scan. Bitmap access can reduce heap-page visits when many qualifying tuples are clustered on relatively few pages.
5. Consider index-only scans where appropriate. If all required output columns and predicates are covered by an index, an Index Only Scan may avoid many heap fetches, subject to PostgreSQL's visibility-map requirements.
6. Validate with representative data. Compare execution time, shared-buffer hits and reads, rows removed by filters, heap blocks, and the actual execution plan.
7. Avoid permanently disabling an access method just to improve one query. A global planning change can negatively affect other workloads.

Conclusion
The key lesson is that an index being used does not automatically mean that heap access is minimized.
In this experiment, PostgreSQL's regular Index Scan used cust_no to identify the broader range and then filtered out the excluded rows after fetching their heap tuples. Rewriting the predicate as two disjoint ranges reduced unnecessary heap access, while the Bitmap Heap Scan reduced it further in this particular workload.

Oracle and PostgreSQL can choose different execution strategies for equivalent SQL. Rather than assuming that one database will always evaluate negative predicates more efficiently, examine the actual execution plan and identify where the predicate is applied.

For PostgreSQL, UNION ALL rewrites, bitmap access paths, and appropriate index design are practical tools for reducing unnecessary heap access and improving SQL performance.

 


댓글목록

목록 글쓰기 다음글

postgresdba.com