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

why Postgresql Explain Cost is Low in Limit and Result Phase but high in Index Scan phase

In the shared plan index scan cost is very high (cost=0.56..696715.50

But Limit (cost=0.56..224.59 is very low

and so on result cost Result (cost=224.59..224.60 rows=1 width=8)

why it is so?

Right now I will consider this query harmless as it looks like the final cost is 224.59 only on CPU so this is not a CPU loading query.

Postgresql version is 14.

explain analyze 
select min(ticketenti0_.created_at) as col_0_0_
  from ticket ticketenti0_
 where ticketenti0_.status=0
   and ticketenti0_.is_spam_ticket='false'
   and ticketenti0_.portal_id=0000
   and (ticketenti0_.group in (14 , 15 , 868 , 15 , 868 , 14));

QUERY PLAN

Result  (cost=224.59..224.60 rows=1 width=8) (actual time=9275.462..9275.464 rows=1 loops=1)   
InitPlan 1 (returns $0)     
->  Limit  (cost=0.56..224.59 rows=1 width=8) (actual time=9275.460..9275.461 rows=0 loops=1)           
      ->  Index Scan using idx_ticket_created_at_status_portal_id_group on ticket ticketenti0_  
           (cost=0.56..696715.50 rows=3110 width=8) (actual time=9275.458..9275.458 rows=0 loops=1)                 
          Index Cond: ((created_at IS NOT NULL) AND (status = 0) AND (portal_id = 0000))                 
          Filter: ((NOT is_spam_ticket) AND (group = ANY ('{14,15,868,15,868,14}'::bigint[])))                 
          Rows Removed by Filter: 148605

Planning Time: 0.926 ms
Execution Time: 9275.498 ms
Tags:

2

Answers


  1. 0

    It’s not totally clear what your question is.

    This query can be accelerated by a covering index:

    CREATE INDEX idx_status_spam_portal_group_created
       ON ticket USING BTREE
       (status, is_spam_ticket, portal_id, group, created_at);

    Your query can be satisfied by a series of range scans on that index.

    Login or Signup to reply.
  2. 0

    The cost estimate reported in the index scan is the estimate for if it were to run to completion. The LIMIT node then de-rates that estimate to account for the (estimated) early stopping.

    Right now I will consider this query harmless as it looks like the final cost is 224.59 only on CPU so this is not a CPU loading query.

    The query take 9 seconds. Most people would consider that to be too slow. That it is poorly estimated should not be a cause of comfort, when you have the actual time staring you in the face.

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

Back To Top
Search