PostgreSQL query takes a long time (as it’s not using the index)

postgresqlpostgresql-12postgresql-performance

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 > (select CHECKPOINT1_PKEVENT FROM SYSTEM_CONTROL)

If however, I run this one, it comes back in seconds.

select count(*)
FROM EVENT 
WHERE PK_EVENT > (1000755073)

Both the PK_EVENT & CHECKPOINT1_PKEVENT columns are defined as "int8", 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)
    ->  Seq Scan on system_control  (cost=0.00..1.01 rows=1 width=8)
  ->  Gather  (cost=2909960.58..2909960.79 rows=2 width=8)
        Workers Planned: 2
        Params Evaluated: $0
        ->  Partial Aggregate  (cost=2908960.58..2908960.59 rows=1 width=8)
              ->  Parallel Seq Scan on event  (cost=0.00..2866882.04 rows=16831415 width=0)
                    Filter: (pk_event > $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)
  ->  Index Only Scan using event_pkey on event  (cost=0.57..11.36 rows=25 width=0)
        Index Cond: (pk_event > 1000755073)

Best Answer

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?

Related Question