explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qspj

Settings
# exclusive inclusive rows x rows loops node
1. 94.754 6,269.831 ↓ 0.0 0 1

Delete on receipts (cost=661,000.33..661,846.65 rows=100 width=34) (actual time=6,269.831..6,269.831 rows=0 loops=1)

2. 0.188 6,175.077 ↑ 1.0 100 1

Nested Loop (cost=661,000.33..661,846.65 rows=100 width=34) (actual time=6,174.267..6,175.077 rows=100 loops=1)

3. 0.138 6,174.289 ↑ 1.0 100 1

HashAggregate (cost=660,999.90..661,000.90 rows=100 width=32) (actual time=6,174.216..6,174.289 rows=100 loops=1)

  • Group Key: "ANY_subquery".id
4. 0.102 6,174.151 ↑ 1.0 100 1

Subquery Scan on ANY_subquery (cost=658,837.51..660,999.65 rows=100 width=32) (actual time=6,158.899..6,174.151 rows=100 loops=1)

5. 0.016 6,174.049 ↑ 1.0 100 1

Limit (cost=658,837.51..660,998.65 rows=100 width=4) (actual time=6,158.827..6,174.049 rows=100 loops=1)

6. 61.477 6,174.033 ↑ 7,668.0 100 1

Nested Loop Semi Join (cost=658,837.51..17,230,369.69 rows=766,796 width=4) (actual time=6,158.825..6,174.033 rows=100 loops=1)

7. 119.518 6,001.306 ↑ 6.9 111,250 1

Merge Anti Join (cost=658,837.23..11,200,808.55 rows=766,821 width=8) (actual time=3,061.046..6,001.306 rows=111,250 loops=1)

  • Merge Cond: (receipts_1.id = runs.receipt_id)
8. 2,634.471 2,634.471 ↑ 2.4 423,916 1

Index Scan using receipts_pkey on receipts receipts_1 (cost=0.43..10,519,976.57 rows=1,032,406 width=8) (actual time=0.020..2,634.471 rows=423,916 loops=1)

  • Filter: (age((('now'::cstring)::date)::timestamp without time zone, inserted_at) > '90 days'::interval)
9. 36.317 3,247.317 ↑ 5.4 333,302 1

Materialize (cost=658,833.84..667,909.65 rows=1,815,162 width=4) (actual time=3,052.584..3,247.317 rows=333,302 loops=1)

10. 1,567.950 3,211.000 ↑ 5.4 333,302 1

Sort (cost=658,833.84..663,371.74 rows=1,815,162 width=4) (actual time=3,052.576..3,211.000 rows=333,302 loops=1)

  • Sort Key: runs.receipt_id
  • Sort Method: external merge Disk: 24,960kB
11. 1,643.050 1,643.050 ↓ 1.0 1,828,753 1

Seq Scan on runs (cost=0.00..420,495.62 rows=1,815,162 width=4) (actual time=0.013..1,643.050 rows=1,828,753 loops=1)

12. 111.250 111.250 ↓ 0.0 0 111,250

Index Scan using projects_pkey on projects (cost=0.28..7.85 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=111,250)

  • Index Cond: (id = receipts_1.project_id)
  • Filter: (plan_id = 1)
  • Rows Removed by Filter: 1
13. 0.600 0.600 ↑ 1.0 1 100

Index Scan using receipts_pkey on receipts (cost=0.43..8.45 rows=1 width=10) (actual time=0.006..0.006 rows=1 loops=100)

  • Index Cond: (id = "ANY_subquery".id)
Planning time : 2.021 ms
Execution time : 149,745.789 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint runs_receipt_id_fkey 143,458.626 ms 100 1,434.586 ms
for constraint receipt_job_states_receipt_id_fkey 11.549 ms 100 0.115 ms