explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jS1Z

Settings
# exclusive inclusive rows x rows loops node
1. 17.583 173,719.928 ↓ 30,889.0 30,889 1

Nested Loop (cost=31,195.85..37,767.71 rows=1 width=361) (actual time=13,209.722..173,719.928 rows=30,889 loops=1)

  • Buffers: shared hit=224347906 read=20606, temp read=1802 written=1802
2. 45.585 13,246.115 ↓ 990.0 990 1

Aggregate (cost=31,083.31..31,083.33 rows=1 width=28) (actual time=13,160.826..13,246.115 rows=990 loops=1)

  • Buffers: shared hit=19804532 read=20606, temp read=1802 written=1802
3. 170.332 13,200.530 ↓ 350,893.0 350,893 1

Sort (cost=31,083.31..31,083.32 rows=1 width=28) (actual time=13,160.748..13,200.53 rows=350,893 loops=1)

  • Sort Key: "Issue_1".business_id, "BookkeepingExport_1".month
  • Sort Method: external merge Disk: 14408kB
  • Buffers: shared hit=19804532 read=20606, temp read=1802 written=1802
4. 2,046.364 13,030.198 ↓ 350,893.0 350,893 1

Nested Loop (cost=1,001.42..31,083.3 rows=1 width=28) (actual time=871.477..13,030.198 rows=350,893 loops=1)

  • Buffers: shared hit=19804532 read=20606
5. 737.292 4,478.919 ↓ 224,307.4 6,504,915 1

Nested Loop (cost=1,001.14..31,074.02 rows=29 width=44) (actual time=21.394..4,478.919 rows=6,504,915 loops=1)

  • Buffers: shared hit=3097978 read=20606
6. 0.000 25.797 ↓ 1,590.0 1,590 1

Gather (cost=1,000.72..30,973.13 rows=1 width=28) (actual time=21.382..25.797 rows=1,590 loops=1)

  • Buffers: shared hit=11813 read=20380
7. 131.418 326.347 ↓ 795.0 795 2

Nested Loop (cost=0.72..29,973.03 rows=1 width=28) (actual time=21.391..326.347 rows=795 loops=2)

  • Buffers: shared hit=11813 read=20380
8. 4.080 194.764 ↓ 20.9 795 2

Nested Loop (cost=0.29..29,895.42 rows=38 width=44) (actual time=21.377..194.764 rows=795 loops=2)

  • Buffers: shared hit=5304 read=19784
9. 190.680 190.680 ↓ 1.7 795 2

Seq Scan on IssueStepHistory IssueStepHistory (cost=0..29,306.85 rows=458 width=24) (actual time=21.361..190.68 rows=795 loops=2)

  • Filter: ("IssueStepHistory".step_name = 'BreakdownBookingsBatchedTaxPaymentsStep'::text)
  • Buffers: shared hit=491 read=19784
10. 0.004 0.004 ↑ 1.0 1 1,590

Index Scan using "Event_pkey" on Event e_1 (cost=0.29..1.28 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=1,590)

  • Index Cond: (e_1.issue_ptr_id = "IssueStepHistory".issue_id)
  • Buffers: shared hit=4813
11. 0.165 0.165 ↑ 1.0 1 1,590

Index Scan using "Issue_pkey" on Issue Issue_1 (cost=0.43..2.03 rows=1 width=32) (actual time=0.163..0.165 rows=1 loops=1,590)

  • Index Cond: ("Issue_1".id = e_1.issue_ptr_id)
  • Filter: (("Issue_1".issue_type)::text = 'month_end_closing'::text)
  • Buffers: shared hit=6509 read=596
12. 3,715.830 3,715.830 ↓ 1.5 4,091 1,590

Index Scan using "Bookkeeping_2f4e4ac4" on Bookkeeping book (cost=0.42..72.86 rows=2,803 width=32) (actual time=0.008..2.337 rows=4,091 loops=1,590)

  • Index Cond: (book.business_id = "Issue_1".business_id)
  • Buffers: shared hit=3086165 read=226
13. 6,504.915 6,504.915 ↓ 0.0 0 6,504,915

Index Scan using "BookkeepingExport_pkey" on BookkeepingExport BookkeepingExport_1 (cost=0.28..0.31 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=6,504,915)

  • Index Cond: ("BookkeepingExport_1".id = book.export_id)
  • Filter: ("BookkeepingExport_1".month >= (date_trunc('month'::text, now()) - '6 mons'::interval))
  • Buffers: shared hit=16706554
14. 3,656.056 160,456.230 ↓ 31.0 31 990

Nested Loop (cost=112.54..6,684.35 rows=1 width=337) (actual time=78.976..162.077 rows=31 loops=990)

  • Buffers: shared hit=204543374
15. 7,920.280 144,817.200 ↓ 6,052.0 6,052 990

Nested Loop (cost=112.11..6,683.68 rows=1 width=353) (actual time=11.93..146.28 rows=6,052 loops=990)

  • Buffers: shared hit=179394080
16. 7,606.995 69,473.250 ↓ 11,350.8 68,105 990

Nested Loop (cost=111.82..6,681.76 rows=6 width=44) (actual time=11.882..70.175 rows=68,105 loops=990)

  • Buffers: shared hit=39008285
17. 901.744 19,599.030 ↓ 244.0 244 990

Nested Loop (cost=111.39..6,627.83 rows=1 width=44) (actual time=11.875..19.797 rows=244 loops=990)

  • Buffers: shared hit=5216256
18. 13.324 6,994.350 ↓ 9.0 9 990

Nested Loop (cost=111.39..6,500.61 rows=1 width=28) (actual time=2.876..7.065 rows=9 loops=990)

  • Buffers: shared hit=4755012
19. 6,061.770 6,944.850 ↓ 1.8 9 990

Bitmap Heap Scan on Issue Issue (cost=111.11..6,497.15 rows=5 width=40) (actual time=2.865..7.015 rows=9 loops=990)

  • Filter: (("Issue".issue_type)::text = 'month_end_closing'::text)
  • Buffers: shared hit=4727485
20. 883.080 883.080 ↓ 1.5 9,468 990

Bitmap Index Scan on "Issue_2f4e4ac4" (cost=0..111.11 rows=6,250 width=0) (actual time=0.892..0.892 rows=9,468 loops=990)

  • Index Cond: ("Issue_1".business_id = "Issue".business_id)
  • Buffers: shared hit=81348
21. 36.176 36.176 ↑ 1.0 1 9,044

Index Scan using "Event_pkey" on Event e (cost=0.29..0.68 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=9,044)

  • Index Cond: (e.issue_ptr_id = "Issue".id)
  • Buffers: shared hit=27527
22. 11,702.936 11,702.936 ↓ 1.2 1,239 9,044

Seq Scan on BookkeepingExport BookkeepingExport (cost=0..111.38 rows=1,056 width=20) (actual time=0.002..1.294 rows=1,239 loops=9,044)

  • Filter: ("BookkeepingExport".month >= (date_trunc('month'::text, now()) - '6 mons'::interval))
  • Buffers: shared hit=461244
23. 42,267.225 42,267.225 ↑ 1.2 279 241,527

Index Scan using "Bookkeeping_eebf80ab" on Bookkeeping b (cost=0.42..50.6 rows=333 width=32) (actual time=0.005..0.175 rows=279 loops=241,527)

  • Index Cond: (b.export_id = "BookkeepingExport".id)
  • Buffers: shared hit=33792029
24. 67,423.670 67,423.670 ↓ 0.0 0 67,423,670

Index Scan using record_corrections_history_record_id_index on record_corrections_history rch (cost=0.29..0.31 rows=1 width=309) (actual time=0.001..0.001 rows=0 loops=67,423,670)

  • Index Cond: (rch.record_id = b.source_record_id)
  • Buffers: shared hit=140385795
25. 11,982.974 11,982.974 ↑ 1.0 1 5,991,487

Index Scan using "Record_pkey" on Record r (cost=0.43..0.65 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=5,991,487)

  • Index Cond: (r.id = b.source_record_id)
  • Buffers: shared hit=25149294