1

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';
7
  • 4
    The query runs in 24 milliseconds - how fast do you need that to be?
    – user1822
    Oct 28, 2021 at 20:48
  • If you are running with default settings of the various cost params, that does seem rather high of an estimate for this query. Are you using the defaults? What is the size of both table and index? If you freshly VACUUM ANALYZE the table, does anything change?
    – jjanes
    Oct 29, 2021 at 0:37
  • I'm surprised nobody has asked this but can you give the DDL for the task table? From here: It has turned out to be very beneficial to adjust random_page_cost in postgresql.conf to a value close to 1 (instead of 4 which has been the default for many years) when running PostgreSQL on SSDs.
    – Vérace
    Oct 29, 2021 at 6:00
  • I'm surprised nobody has asked this but can you give the DDL for the task table? From here: "It has turned out to be very beneficial to adjust random_page_cost in postgresql.conf to a value close to 1 (instead of 4 which has been the default for many years) when running PostgreSQL on SSDs". Finally, why have situation <> ALL ('{4,5}'::integer[]) and not the more traditional situation NOT IN (4, 5)?
    – Vérace
    Oct 29, 2021 at 6:21
  • @Vérace The planner rewrites situation NOT IN (4, 5) to be situation <> ALL ('{4,5}'::integer[]) so he might have just written the index WHERE to match what was showing up in the pre-index explain output.
    – jjanes
    Oct 29, 2021 at 17:01

3 Answers 3

4

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:

4
  • In the example I have reduced the number of columns (task.deadline, task.id), but in the real environment I have about four more columns from the task table. In this case, do I create the index to have all these other columns or just the ID?
    – Tom
    Oct 29, 2021 at 12:32
  • 1
    @Tom: That depends on the complete situation. Most importantly, it those additional columns are small and rarely updated (and the table row is much wider), it might be a good idea. Oct 29, 2021 at 13:13
  • A question that may seem a little silly. This database is almost 10 years old, that is, strange or not-so-functional indexes were created. Poorly crafted queries were also created. Is there any good method to be able to track these poorly constructed indexes, or queries that can have improved? As I learned today with @Laurenz-albe, even the order of columns in the index is something that affects performance. That's why I think we'll have to review all our queries and indexes. I'm not a DBA, just a dev, so unfortunately I don't have that deep knowledge to have created everything correctly.
    – Tom
    Oct 29, 2021 at 19:35
  • You say: The index column approved is just dead freight with the condition approved = 'N'. Remove it. - what if approved was 50% Y and 50% N? Or maybe very few are N (as appears to be the case - at least with the other conditions) - it would be very discriminating?
    – Vérace
    Oct 30, 2021 at 10:06
7

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.

3
  • 1
    The absolute value of the cost estimate is what gets compared to the various jit_*above_cost settings, so this does give them some weak meaning on an absolute scale. And note that 2/3 of his time seems to be spent on JIT, so the absolute value being higher than jit_above_cost is not something of no consequence. Besides, while someone could go in and double every setting (including the JIT ones), few actually do such silly things.
    – jjanes
    Oct 29, 2021 at 0:52
  • 1
    @jjanes: I usually disable JIT completely. In typical OLTP environments I have never seen this improve things, but always make things slower. DWH environments might be different though.
    – user1822
    Oct 29, 2021 at 7:17
  • 1
    @a_horse_with_no_name Yes, I think turning JIT on by default in v12 was a mistake, and think it still is in v14.
    – jjanes
    Oct 29, 2021 at 16:09
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).

8
  • I'm not sure I understand correctly, but does the order that the table's columns are placed in the index affect its performance?
    – Tom
    Oct 29, 2021 at 12:40
  • 1
    Yes, very much so. But in this case I am not certain which order is better. Oct 29, 2021 at 12:42
  • 1
    @Tom: It depends on the number of values in the IN list filtering situation. For only a view values the current order of columns (situation, deadline) is typically best, as columns for range filter should come after equality filters. See: dba.stackexchange.com/a/33220/3684 Oct 29, 2021 at 13:10
  • 1
    Then the original order must have been the better one. Oct 29, 2021 at 13:27
  • 1
    @Tom Order of columns is fundamentally important in btree indexes. See: dba.stackexchange.com/a/27493/3684 Also matters for GiST indexes (in a different way). Currently unimportant for other index types. See: postgresql.org/docs/current/indexes-multicolumn.html Oct 29, 2021 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.