High cost in PostgreSQL query

Question

I have a table that has more than 10.000.000 records, and I'm creating a query that returns about 4436 records.

It just so happens that it gives me the impression that the query cost to get to the last record is very high.

Index Scan using idx_name on task  (cost=0.28..142102.57 rows=3470 width=34) (actual time=14.690..22.894 rows=4436 loops=1)
"  Index Cond: ((situation = ANY ('{0,1,2,3,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}'::integer[])) AND (deadline < CURRENT_TIMESTAMP))"
Planning Time: 1.335 ms
JIT:
  Functions: 5
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.654 ms, Inlining 0.000 ms, Optimization 1.214 ms, Emission 13.163 ms, Total 16.030 ms
Execution Time: 24.758 ms

Is this level of cost acceptable or does this index need improvement?

Index:

CREATE INDEX idx_name ON task (situation, deadline, approved)
WHERE
deadline IS NOT NULL AND
situation <> ALL ('{4,5}'::integer[]) AND
approved = 'N';

My Query:

SELECT
        task.deadline,
        task.id
    FROM
        task
    WHERE
        task.deadline IS NOT NULL
        AND task.situation IN ('0', '1', '2', '3', '6' ,'7' ,'8','9','10','11','12','13','14','15','16','17','18','19','20')
        AND task.situation NOT IN ('4', '5')
        AND task.deadline < CURRENT_TIMESTAMP
        AND task.approved = 'N';

Answer 1

The index is good, the query is fast.

But the index can be better and the query faster. The index column approved is just dead freight with the condition approved = 'N'. Remove it.

CREATE INDEX idx_name ON task (situation, deadline)
WHERE  deadline IS NOT NULL
AND    situation <> ALL ('{4,5}'::integer[])
AND    approved = 'N';

Matters for index size, even if approved is varchar(1) (and probably should be boolean instead). Since deadline is an (aligned) timestamp (with time zone) type, the added index column approved wastes at least 8 bytes per index tuple - makes it grow by 1/3.

Better yet, if the example with SELECT deadline, id ... is any indication, it should pay to append id as "included" column to allow index-only scans:

CREATE INDEX idx_name ON task (situation, deadline) INCLUDE (id)
WHERE  deadline IS NOT NULL
AND    situation <> ALL ('{4,5}'::integer[])
AND    approved = 'N';

Requires Postgres 11 or later. Brings the size back to what it was before.

See:

• Does a query with a primary key and foreign keys run faster than a query with just primary keys?
• Slow index scans in large table

Answer 2

As hinted at in a comment, you shouldn't be looking at the query cost, but rather at its actual run time (or you shouldn't be bothered by any of it at all if the run time is acceptable). The estimated plan cost is not an indication of anything except the relative amount of various resources Postgres estimates it might need to spend to execute this particular plan compared to other possible plans.

Looking at the absolute cost value tells you absolutely nothing; comparing it with other plans' costs tells you which of them Postgres thinks is more efficient, based on the information the optimizer has.

See also this Q&A.

---

Looking into the horse's mouth one might see this:

/*-------------------------------------------------------------------------
 *
 * costsize.c
 *    Routines to compute (and set) relation sizes and path costs
 *
 * Path costs are measured in arbitrary units established by these basic
 * parameters:
 *
 *  seq_page_cost       Cost of a sequential page fetch
 *  random_page_cost    Cost of a non-sequential page fetch
 *  cpu_tuple_cost      Cost of typical CPU time to process a tuple
 *  cpu_index_tuple_cost  Cost of typical CPU time to process an index tuple
 *  cpu_operator_cost   Cost of CPU time to execute an operator or function
 *  parallel_tuple_cost Cost of CPU time to pass a tuple from worker to leader backend
 *  parallel_setup_cost Cost of setting up shared memory for parallelism
...

and further down that path:

# - Planner Cost Constants -

#seq_page_cost = 1.0            # measured on an arbitrary scale
#random_page_cost = 4.0         # same scale as above
#cpu_tuple_cost = 0.01          # same scale as above
#cpu_index_tuple_cost = 0.005       # same scale as above
#cpu_operator_cost = 0.0025     # same scale as above

You'll notice that they emphasize "arbitrary units" on an "arbitrary scale"; all they want to establish is that reading N pages at random is four times as resource-intensive ("expensive") as reading that many pages sequentially, or that evaluating a predicate against an index entry is half as expensive as doing that against a table row. When the cost of the entire plan tree is added up, you get a value that can only be compared to another tree cost; it can only be higher or lower, not high or low.

---

HT to jjanes for mentioning JIT in a comment. The estimated query cost in your case happens to just exceed the JIT trigger threshold, which defaults to 100000, and, as jjanes astutely pointed out, "2/3 of [your query] time seems to be spent on JIT", which is probably counterproductive. You might want to evaluate if having JIT enabled in your environment is useful.

Answer 3

Both the other answers are very good, let me add one detail:

Both of your conditions are not simple equality comparisons, so the second column in the index will only be used to filter out rows (before the table is accessed). So the order of index columns will influence how many index entries have to be read to find the answer.

To find the best index for the query, first try with an index on (situation, deadline), then drop that index and try with one on (deadline, situation), and see which one of them touches fewer blocks with EXPLAIN (ANALYZE, BUFFERS).