PostgreSQL query takes a long time (as it's not using the index) This query takes a long time, as I guess it's doing a full-table scan. It works ok in Oracle, just not in Postgres 12.
select count(*)
FROM EVENT 
WHERE PK_EVENT &gt; (select CHECKPOINT1_PKEVENT FROM SYSTEM_CONTROL) 

If however, I run this one, it comes back in seconds.
select count(*)
FROM EVENT 
WHERE PK_EVENT &gt; (1000755073)

Both the PK_EVENT &amp; CHECKPOINT1_PKEVENT columns are defined as &quot;int8&quot;, as they could get very large. EVENT.PK_EVENT is the primary key. I've tried re-arranging the query, but so far nothing works. The count(*) only results in 2537 rows i.e. the number of rows generated today on our test Pg database.
Query plans :
using system_control:-
Finalize Aggregate  (cost=2909961.80..2909961.81 rows=1 width=8)
  InitPlan 1 (returns $0)
    -&gt;  Seq Scan on system_control  (cost=0.00..1.01 rows=1 width=8)
  -&gt;  Gather  (cost=2909960.58..2909960.79 rows=2 width=8)
        Workers Planned: 2
        Params Evaluated: $0
        -&gt;  Partial Aggregate  (cost=2908960.58..2908960.59 rows=1 width=8)
              -&gt;  Parallel Seq Scan on event  (cost=0.00..2866882.04 rows=16831415 width=0)
                    Filter: (pk_event &gt; $0)
JIT:
  Functions: 8
  Options: Inlining true, Optimization true, Expressions true, Deforming true

using hardcoded value:-
Aggregate  (cost=11.42..11.43 rows=1 width=8)
  -&gt;  Index Only Scan using event_pkey on event  (cost=0.57..11.36 rows=25 width=0)
        Index Cond: (pk_event &gt; 1000755073)
The entire query is planned up front, and at the time of planning it doesn't know what value will be found in CHECKPOINT1_PKEVENT.  It makes the generic assumption that the inequality will match 1/3 of the rows, which is obviously quite wrong.  When faced with this situation, I usually just have the client software run the queries separately, stuffing the result of one into a parameter for the other, assuming I can tolerate the prospect that the value may have changed between the two executions.
That said, it should probably be doing an index only scan anyway.  Do you have a very high setting for random_page_cost?  What are you other planner settings?  Has the table EVENT been vacuumed recently?