explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EBOe

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 4,351.032 ↑ 11.0 1 1

Group (cost=714,596.84..714,597.01 rows=11 width=104) (actual time=4,350.977..4,351.032 rows=1 loops=1)

  • Group Key: billings.final_amount, billings.discount, (COALESCE(anon_1.paid_amount, '0'::numeric)), (COALESCE(anon_2.opened_amount, '0'::numeric)), (COALESCE(anon_3.due_amount, '0'::numeric))
  • Buffers: shared hit=1632152 read=12145, temp read=1279 written=1277
2. 0.112 4,350.988 ↓ 5.5 60 1

Sort (cost=714,596.84..714,596.87 rows=11 width=104) (actual time=4,350.975..4,350.988 rows=60 loops=1)

  • Sort Key: billings.final_amount, billings.discount, (COALESCE(anon_1.paid_amount, '0'::numeric)), (COALESCE(anon_2.opened_amount, '0'::numeric)), (COALESCE(anon_3.due_amount, '0'::numeric))
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=1632152 read=12145, temp read=1279 written=1277
3. 0.124 4,350.876 ↓ 5.5 60 1

Hash Join (cost=587,505.47..714,596.65 rows=11 width=104) (actual time=4,171.041..4,350.876 rows=60 loops=1)

  • Buffers: shared hit=1632152 read=12145, temp read=1279 written=1277
4. 99.554 3,833.713 ↓ 5.5 60 1

Nested Loop (cost=504,742.03..631,833.19 rows=11 width=76) (actual time=3,653.938..3,833.713 rows=60 loops=1)

  • Buffers: shared hit=1565802 read=8150, temp read=1279 written=1277
5. 0.007 1,604.339 ↓ 5.5 60 1

Hash Join (cost=212,206.32..285,674.29 rows=11 width=44) (actual time=1,604.215..1,604.339 rows=60 loops=1)

  • Buffers: shared hit=750304 read=4123
6. 0.000 117.664 ↓ 5.5 60 1

Gather (cost=7,861.53..81,329.47 rows=11 width=12) (actual time=117.487..117.664 rows=60 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=84764 read=4123
7. 67.074 238.203 ↓ 4.0 20 3 / 3

Hash Join (cost=6,861.53..80,328.37 rows=5 width=12) (actual time=149.484..238.203 rows=20 loops=3)

  • Buffers: shared hit=84764 read=4123
8. 157.115 157.115 ↑ 1.2 181,211 3 / 3

Seq Scan on receipts receipts (cost=0..72,619.59 rows=225,920 width=4) (actual time=0.838..157.115 rows=181,211 loops=3)

  • Filter: ((receipts.deleted_at IS NULL) AND receipts.is_active)
  • Buffers: shared hit=66222 read=4123
9. 0.027 14.014 ↓ 5.5 60 3 / 3

Hash (cost=6,861.39..6,861.39 rows=11 width=16) (actual time=14.014..14.014 rows=60 loops=3)

  • Buffers: shared hit=18488
10. 0.038 13.987 ↓ 5.5 60 3 / 3

Nested Loop (cost=4.63..6,861.39 rows=11 width=16) (actual time=8.634..13.987 rows=60 loops=3)

  • Buffers: shared hit=18488
11. 13.884 13.884 ↑ 1.0 1 3 / 3

Seq Scan on billings billings (cost=0..6,754.32 rows=1 width=12) (actual time=8.598..13.884 rows=1 loops=3)

  • Filter: ((billings.external_id)::text = '7bb90ad80c7b1cddc5bd0478390773a5eaa9e5ac'::text)
  • Buffers: shared hit=18423
12. 0.046 0.065 ↓ 2.3 60 3 / 3

Bitmap Heap Scan on installments installments (cost=4.63..106.81 rows=26 width=8) (actual time=0.026..0.065 rows=60 loops=3)

  • Heap Blocks: exact=18
  • Buffers: shared hit=65
13. 0.019 0.019 ↓ 2.3 60 3 / 3

Bitmap Index Scan on ix_installments_billing_id (cost=0..4.62 rows=26 width=0) (actual time=0.019..0.019 rows=60 loops=3)

  • Index Cond: (installments.billing_id = billings.billing_id)
  • Buffers: shared hit=11
14. 3.848 1,486.668 ↑ 2.0 10,005 1

Hash (cost=204,094.41..204,094.41 rows=20,030 width=36) (actual time=1,486.668..1,486.668 rows=10,005 loops=1)

  • Buffers: shared hit=665540
15. 4.295 1,482.820 ↑ 2.0 10,005 1

Subquery Scan on anon_2 (cost=203,643.74..204,094.41 rows=20,030 width=36) (actual time=1,471.886..1,482.82 rows=10,005 loops=1)

  • Buffers: shared hit=665540
16. 123.114 1,478.525 ↑ 2.0 10,005 1

HashAggregate (cost=203,643.74..203,894.11 rows=20,030 width=36) (actual time=1,471.884..1,478.525 rows=10,005 loops=1)

  • Group Key: installments_1.billing_id
  • Buffers: shared hit=665540
17. 254.972 1,355.411 ↓ 2.9 185,361 1

Bitmap Heap Scan on installments installments_1 (cost=5,746.22..203,318.55 rows=65,037 width=10) (actual time=38.057..1,355.411 rows=185,361 loops=1)

  • Filter: ((installments_1.deleted_at IS NULL) AND (installments_1.canceled_at IS NULL) AND ((SubPlan 2) >= (now())::date))
  • Heap Blocks: exact=23019
  • Buffers: shared hit=665540
18. 34.594 34.594 ↓ 1.0 229,324 1

Bitmap Index Scan on ix_installments_status (cost=0..5,729.97 rows=220,472 width=0) (actual time=34.594..34.594 rows=229,324 loops=1)

  • Index Cond: ((installments_1.status)::text = 'OPENED'::text)
  • Buffers: shared hit=1102
19.          

SubPlan (for Bitmap Heap Scan)

20. 213.169 1,065.845 ↑ 1.0 1 213,169

Result (cost=0.71..0.72 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=213,169)

  • Buffers: shared hit=641419
21.          

Initplan (for Result)

22. 426.338 852.676 ↑ 1.0 1 213,169

Limit (cost=0.29..0.71 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=213,169)

  • Buffers: shared hit=641419
23. 426.338 426.338 ↑ 38.0 1 213,169

Index Scan using ix_dates_date_value on dates dates (cost=0.29..16.15 rows=38 width=4) (actual time=0.002..0.002 rows=1 loops=213,169)

  • Index Cond: ((dates.date_value IS NOT NULL) AND (dates.date_value >= (installments_1.due_date)::date) AND (dates.date_value <= ((installments_1.due_date)::date + '7 days'::interval)))
  • Filter: ((dates.weekday >= 1) AND (dates.weekday <= 5) AND ((dates.brazil_holiday)::text = 'Non Brazil Holiday'::text))
  • Buffers: shared hit=641419
24. 85.942 2,129.820 ↑ 2.8 7,124 60

Materialize (cost=292,535.71..342,904.03 rows=20,030 width=36) (actual time=30.146..35.497 rows=7,124 loops=60)

  • Buffers: shared hit=815498 read=4027, temp read=1279 written=1277
25. 3.590 2,043.878 ↑ 2.8 7,124 1

Subquery Scan on anon_3 (cost=292,535.71..342,803.88 rows=20,030 width=36) (actual time=1,808.731..2,043.878 rows=7,124 loops=1)

  • Buffers: shared hit=815498 read=4027, temp read=1279 written=1277
26. 83.612 2,040.288 ↑ 2.8 7,124 1

GroupAggregate (cost=292,535.71..342,603.58 rows=20,030 width=36) (actual time=1,808.73..2,040.288 rows=7,124 loops=1)

  • Group Key: installments_2.billing_id
  • Buffers: shared hit=815498 read=4027, temp read=1279 written=1277
27. 23.526 1,817.636 ↑ 2.3 27,808 1

Sort (cost=292,535.71..292,695.94 rows=64,090 width=27) (actual time=1,808.65..1,817.636 rows=27,808 loops=1)

  • Sort Key: installments_2.billing_id
  • Sort Method: quicksort Memory: 2941kB
  • Buffers: shared hit=731858 read=4027, temp read=1279 written=1277
28. 230.040 1,794.110 ↑ 2.3 27,808 1

Hash Join (cost=204,513.51..287,418.83 rows=64,090 width=27) (actual time=1,245.939..1,794.11 rows=27,808 loops=1)

  • Buffers: shared hit=731858 read=4027, temp read=1279 written=1277
29. 319.891 319.891 ↓ 1.0 543,633 1

Seq Scan on receipts receipts_1 (cost=0..75,804.01 rows=542,207 width=13) (actual time=0.062..319.891 rows=543,633 loops=1)

  • Filter: ((receipts_1.deleted_at IS NULL) AND receipts_1.is_active)
  • Buffers: shared hit=66318 read=4027
30. 15.284 1,244.179 ↑ 2.3 27,808 1

Hash (cost=203,318.55..203,318.55 rows=65,037 width=22) (actual time=1,244.178..1,244.179 rows=27,808 loops=1)

  • Buffers: shared hit=665540, temp written=82
31. 361.904 1,228.895 ↑ 2.3 27,808 1

Bitmap Heap Scan on installments installments_2 (cost=5,746.22..203,318.55 rows=65,037 width=22) (actual time=17.792..1,228.895 rows=27,808 loops=1)

  • Filter: ((installments_2.deleted_at IS NULL) AND (installments_2.canceled_at IS NULL) AND ((SubPlan 6) < (now())::date))
  • Heap Blocks: exact=23019
  • Buffers: shared hit=665540
32. 14.315 14.315 ↓ 1.0 229,324 1

Bitmap Index Scan on ix_installments_status (cost=0..5,729.97 rows=220,472 width=0) (actual time=14.315..14.315 rows=229,324 loops=1)

  • Index Cond: ((installments_2.status)::text = 'OPENED'::text)
  • Buffers: shared hit=1102
33.          

SubPlan (for Bitmap Heap Scan)

34. 213.169 852.676 ↑ 1.0 1 213,169

Result (cost=0.71..0.72 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=213,169)

  • Buffers: shared hit=641419
35.          

Initplan (for Result)

36. 213.169 639.507 ↑ 1.0 1 213,169

Limit (cost=0.29..0.71 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=213,169)

  • Buffers: shared hit=641419
37. 426.338 426.338 ↑ 38.0 1 213,169

Index Scan using ix_dates_date_value on dates dates_2 (cost=0.29..16.15 rows=38 width=4) (actual time=0.002..0.002 rows=1 loops=213,169)

  • Index Cond: ((dates_2.date_value IS NOT NULL) AND (dates_2.date_value >= (installments_2.due_date)::date) AND (dates_2.date_value <= ((installments_2.due_date)::date + '7 days'::interval)))
  • Filter: ((dates_2.weekday >= 1) AND (dates_2.weekday <= 5) AND ((dates_2.brazil_holiday)::text = 'Non Brazil Holiday'::text))
  • Buffers: shared hit=641419
38.          

SubPlan (for GroupAggregate)

39. 55.616 139.040 ↑ 1.0 1 27,808

Result (cost=0.71..0.72 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=27,808)

  • Buffers: shared hit=83640
40.          

Initplan (for Result)

41. 27.808 83.424 ↑ 1.0 1 27,808

Limit (cost=0.29..0.71 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=27,808)

  • Buffers: shared hit=83640
42. 55.616 55.616 ↑ 38.0 1 27,808

Index Scan using ix_dates_date_value on dates dates_1 (cost=0.29..16.15 rows=38 width=4) (actual time=0.002..0.002 rows=1 loops=27,808)

  • Index Cond: ((dates_1.date_value IS NOT NULL) AND (dates_1.date_value >= (installments_2.due_date)::date) AND (dates_1.date_value <= ((installments_2.due_date)::date + '7 days'::interval)))
  • Filter: ((dates_1.weekday >= 1) AND (dates_1.weekday <= 5) AND ((dates_1.brazil_holiday)::text = 'Non Brazil Holiday'::text))
  • Buffers: shared hit=83640
43. 15.683 517.039 ↓ 2.1 41,140 1

Hash (cost=82,513.75..82,513.75 rows=19,975 width=36) (actual time=517.039..517.039 rows=41,140 loops=1)

  • Buffers: shared hit=66350 read=3995
44. 17.770 501.356 ↓ 2.1 41,140 1

Subquery Scan on anon_1 (cost=82,064.31..82,513.75 rows=19,975 width=36) (actual time=454.668..501.356 rows=41,140 loops=1)

  • Buffers: shared hit=66350 read=3995
45. 172.531 483.586 ↓ 2.1 41,140 1

HashAggregate (cost=82,064.31..82,314 rows=19,975 width=36) (actual time=454.666..483.586 rows=41,140 loops=1)

  • Group Key: receipts_2.billing_id
  • Buffers: shared hit=66350 read=3995
46. 311.055 311.055 ↓ 1.7 264,884 1

Seq Scan on receipts receipts_2 (cost=0..81,263.02 rows=160,258 width=8) (actual time=0.059..311.055 rows=264,884 loops=1)

  • Filter: ((receipts_2.deleted_at IS NULL) AND (receipts_2.canceled_at IS NULL) AND receipts_2.is_active AND ((receipts_2.conciliated_at)::date <= (now())::date))
  • Buffers: shared hit=66350 read=3995
Planning time : 1.678 ms
Execution time : 4,351.733 ms