explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4nGM

Settings
# exclusive inclusive rows x rows loops node
1. 123.651 814.286 ↓ 0.0 0 1

Update on reimbursement_discount (cost=176,502.03..190,733.18 rows=2,121 width=91) (actual time=814.286..814.286 rows=0 loops=1)

2.          

CTE teste

3. 44.212 685.868 ↑ 5.4 391 1

Nested Loop (cost=453.11..176,501.61 rows=2,121 width=8) (actual time=149.713..685.868 rows=391 loops=1)

4. 91.241 153.894 ↓ 1.5 54,022 1

Hash Join (cost=452.68..19,241.06 rows=35,809 width=17) (actual time=13.043..153.894 rows=54,022 loops=1)

  • Hash Cond: (d.reimbursement_id = r.id)
5. 60.936 60.936 ↓ 1.0 714,636 1

Seq Scan on reimbursement_discount d (cost=0.00..16,929.17 rows=708,117 width=17) (actual time=0.002..60.936 rows=714,636 loops=1)

6. 0.108 1.717 ↑ 1.0 973 1

Hash (cost=440.51..440.51 rows=973 width=8) (actual time=1.717..1.717 rows=973 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
7. 1.609 1.609 ↑ 1.0 973 1

Seq Scan on reimbursement r (cost=0.00..440.51 rows=973 width=8) (actual time=0.047..1.609 rows=973 loops=1)

  • Filter: (week_number = 19)
  • Rows Removed by Filter: 19161
8. 486.198 486.198 ↓ 0.0 0 54,022

Index Scan using order_order_number_key on "order" o (cost=0.43..3.90 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=54,022)

  • Index Cond: ((order_number)::text = (d.order_number)::text)
  • Filter: ((created_date >= '2020-04-27 03:00:00'::timestamp without time zone) AND (created_date <= '2020-05-04 02:59:59'::timestamp without time zone) AND (last_status = ANY ('{6,9}'::integer[])))
  • Rows Removed by Filter: 1
9.          

SubPlan (for Nested Loop)

10. 0.391 1.564 ↑ 1.0 1 391

Limit (cost=0.29..8.31 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=391)

11. 1.173 1.173 ↑ 1.0 1 391

Index Scan using ix_reimbursement_kpi on reimbursement (cost=0.29..8.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=391)

  • Index Cond: ((year = 2020) AND (week_number = 18) AND (poc_id = r.poc_id))
12. 0.535 690.635 ↑ 5.4 391 1

Nested Loop (cost=0.42..14,231.57 rows=2,121 width=91) (actual time=150.768..690.635 rows=391 loops=1)

13. 686.190 686.190 ↑ 5.4 391 1

CTE Scan on teste (cost=0.00..42.42 rows=2,121 width=40) (actual time=149.719..686.190 rows=391 loops=1)

14. 3.910 3.910 ↑ 1.0 1 391

Index Scan using reimbursement_discount_pkey on reimbursement_discount (cost=0.42..6.68 rows=1 width=47) (actual time=0.010..0.010 rows=1 loops=391)

  • Index Cond: (id = teste.id)
Planning time : 0.502 ms
Execution time : 820.791 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint reimbursement_discount_reimbursement_id_fkey 6.414 ms 391 0.016 ms