PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgre join query not using expected index

I have a very simple two-table set in Postgre 16.0:

CREATE TABLE fk_table (
  fk_id INT PRIMARY KEY
, fk_name VARCHAR(50)
);
CREATE TABLE main_table (
  id INT PRIMARY KEY
, date TIMESTAMP
, fk INT REFERENCES fk_table(fk_id)
--, tens more columns
);

The main_table has a lot of data (some tens of columns, millions of rows), fk_table just a few values.

I have a query I’m trying to speed up:

SELECT MIN(date) FROM main_table
INNER JOIN fk_table ON fk = fk_id
WHERE fk_name = 'pending';

I would expect that adding an index by fk and date would speed things up, but it’s not working:

CREATE INDEX idx__main_table__fk__date ON main_table(fk, date);

The EXPLAIN ANALYZE results:

QUERY PLAN
Finalize Aggregate  (cost=71379.22..71379.23 rows=1 width=8) (actual time=252.046..257.345 rows=1 loops=1)
  ->  Gather  (cost=71379.01..71379.22 rows=2 width=8) (actual time=251.859..257.338 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=70379.01..70379.02 rows=1 width=8) (actual time=192.993..192.995 rows=1 loops=3)
              ->  Hash Join  (cost=8.18..70376.35 rows=1063 width=8) (actual time=0.330..191.925 rows=16564 loops=3)
                    Hash Cond: (fk = fk_id)
                    ->  Parallel Seq Scan on main_table  (cost=0.00..68849.05 rows=574205 width=12) (actual time=0.235..166.496 rows=459364 loops=3)
                    ->  Hash  (cost=8.17..8.17 rows=1 width=4) (actual time=0.030..0.031 rows=1 loops=3)
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          ->  Index Scan using fk_table_fk_name_key on fk_table  (cost=0.15..8.17 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=3)
                                Index Cond: ((fk_name)::text = 'pending'::text)
Planning Time: 1.630 ms
Execution Time: 257.392 ms

However, making a nested query, the index is used:

SELECT MIN(date) FROM main_table
WHERE fk = (SELECT fk_id 
            FROM fk_table 
            WHERE fk_name = 'pending')

EXPLAIN ANALYZE RETURNS:

QUERY PLAN
Result  (cost=8.80..8.81 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Index Scan using fk_table_fk_name_key on fk_table  (cost=0.15..8.17 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)
          Index Cond: ((fk_name)::text = 'pending'::text)
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.43..0.63 rows=1 width=8) (actual time=0.045..0.045 rows=1 loops=1)
          ->  Index Only Scan using idx__main_table__fk__date on main_table  (cost=0.43..92122.34 rows=459364 width=8) (actual time=0.045..0.045 rows=1 loops=1)
                Index Cond: ((fk_name = $0) AND (date IS NOT NULL))
                Heap Fetches: 0
Planning Time: 0.240 ms
Execution Time: 0.070 ms

Why does the first query don’t use the index? Is there something wrong in there, or in the index, or am I missing anything else?

Tags:

2

Answers


  1. 0

    In the first query, it cannot be certain only one row of fk_table will match fk_name = ‘pending’, so it can’t use the ordered index scan method to stop early.

    In the second query, it can be certain as it would be an error if there was more than one row for the subquery, which opens up the benefit of the early-stopping index scan.

    Note that even if there were a unique index on fk_name, the planner still would not use that information.

    Login or Signup to reply.
  2. 0

    Consider a composite index on main_table that includes fk and date if you often need to retrieve the minimum date for specific foreign key values.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search