explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0esZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 4,358.092 ↑ 1.0 1 1

Aggregate (cost=687,057.40..687,057.41 rows=1 width=0) (actual time=4,358.092..4,358.092 rows=1 loops=1)

  • Buffers: shared hit=1,368,157 read=604,889 written=1,155, temp read=114 written=143
2. 0.054 4,358.077 ↑ 1.0 100 1

Nested Loop (cost=686,582.83..687,057.15 rows=100 width=0) (actual time=4,357.790..4,358.077 rows=100 loops=1)

  • Buffers: shared hit=1,368,157 read=604,889 written=1,155, temp read=114 written=143
3. 0.064 4,357.723 ↑ 1.0 100 1

HashAggregate (cost=686,582.40..686,583.40 rows=100 width=4) (actual time=4,357.700..4,357.723 rows=100 loops=1)

  • Group Key: receipts_1.id
  • Buffers: shared hit=1,367,759 read=604,886 written=1,155, temp read=114 written=143
4. 0.013 4,357.659 ↑ 1.0 100 1

Limit (cost=684,426.97..686,581.15 rows=100 width=4) (actual time=4,353.038..4,357.659 rows=100 loops=1)

  • Buffers: shared hit=1,367,759 read=604,886 written=1,155, temp read=114 written=143
5. 196.842 4,357.646 ↑ 7,707.0 100 1

Nested Loop Semi Join (cost=684,426.97..17,286,708.55 rows=770,703 width=4) (actual time=4,353.036..4,357.646 rows=100 loops=1)

  • Buffers: shared hit=1,367,759 read=604,886 written=1,155, temp read=114 written=143
6. 86.851 3,780.955 ↑ 2.0 379,849 1

Merge Anti Join (cost=684,426.70..11,226,418.55 rows=770,729 width=8) (actual time=1,897.090..3,780.955 rows=379,849 loops=1)

  • Merge Cond: (receipts_1.id = runs.receipt_id)
  • Buffers: shared hit=228,653 read=604,877 written=1,155, temp read=114 written=143
7. 1,758.697 1,758.697 ↑ 2.4 434,573 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.019..1,758.697 rows=434,573 loops=1)

  • Filter: (age((('now'::cstring)::date)::timestamp without time zone, inserted_at) > '90 days'::interval)
  • Buffers: shared hit=216,840 read=214,310 written=1,155
8. 7.639 1,935.407 ↑ 28.9 62,918 1

Materialize (cost=684,424.68..693,525.97 rows=1,820,258 width=4) (actual time=1,896.493..1,935.407 rows=62,918 loops=1)

  • Buffers: shared hit=11,813 read=390,567, temp read=114 written=143
9. 99.466 1,927.768 ↑ 28.9 62,918 1

Sort (cost=684,424.68..688,975.33 rows=1,820,258 width=4) (actual time=1,896.490..1,927.768 rows=62,918 loops=1)

  • Sort Key: runs.receipt_id
  • Sort Method: external merge Disk: 1,128kB
  • Buffers: shared hit=11,813 read=390,567, temp read=114 written=143
10. 196.111 1,828.302 ↑ 20.6 88,554 1

Hash Join (cost=77.41..445,379.88 rows=1,820,258 width=4) (actual time=30.527..1,828.302 rows=88,554 loops=1)

  • Hash Cond: (runs.project_id = projects_1.id)
  • Buffers: shared hit=11,813 read=390,567
11. 1,631.489 1,631.489 ↓ 1.0 1,829,585 1

Seq Scan on runs (cost=0.00..420,546.58 rows=1,820,258 width=12) (actual time=0.010..1,631.489 rows=1,829,585 loops=1)

  • Buffers: shared hit=11,777 read=390,567
12. 0.240 0.702 ↓ 1.0 1,646 1

Hash (cost=56.86..56.86 rows=1,644 width=4) (actual time=0.702..0.702 rows=1,646 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 74kB
  • Buffers: shared hit=36
13. 0.462 0.462 ↓ 1.0 1,646 1

Seq Scan on projects projects_1 (cost=0.00..56.86 rows=1,644 width=4) (actual time=0.006..0.462 rows=1,646 loops=1)

  • Filter: (plan_id = 1)
  • Rows Removed by Filter: 25
  • Buffers: shared hit=36
14. 379.849 379.849 ↓ 0.0 0 379,849

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=379,849)

  • Index Cond: (id = receipts_1.project_id)
  • Filter: (plan_id = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1,139,106 read=9
15. 0.300 0.300 ↑ 1.0 1 100

Index Only Scan using receipts_pkey on receipts (cost=0.43..4.73 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (id = receipts_1.id)
  • Heap Fetches: 100
  • Buffers: shared hit=398 read=3
Planning time : 0.824 ms
Execution time : 4,358.721 ms