explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B3fo : Optimization for: monthly; plan #cY6s

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 43,421.544 14,888,701.836 ↑ 92.2 949,264 1

GroupAggregate (cost=72,715,429.43..76,436,262.95 rows=87,549,024 width=132) (actual time=14,788,134.649..14,888,701.836 rows=949,264 loops=1)

  • Group Key: pe.wallet_owner_id, (date_trunc('month'::text, pe.event_time)), pe.gl_center, ue.service_id, bu.balance_template_id
2. 515,621.903 14,845,280.292 ↓ 1.1 92,045,113 1

Sort (cost=72,715,429.43..72,934,301.99 rows=87,549,024 width=47) (actual time=14,788,134.515..14,845,280.292 rows=92,045,113 loops=1)

  • Sort Key: pe.wallet_owner_id, (date_trunc('month'::text, pe.event_time)), pe.gl_center, ue.service_id, bu.balance_template_id
  • Sort Method: external merge Disk: 6061488kB
3. 2,167,937.894 14,329,658.389 ↓ 1.1 92,045,113 1

Hash Join (cost=35,931,152.41..55,779,824.64 rows=87,549,024 width=47) (actual time=7,579,192.038..14,329,658.389 rows=92,045,113 loops=1)

  • Hash Cond: (pe.event_object_id = euq.event_object_id)
4. 1,627,607.265 9,703,633.169 ↓ 1.0 92,045,113 1

Hash Join (cost=24,473,006.71..40,340,537.25 rows=88,577,571 width=63) (actual time=5,121,099.932..9,703,633.169 rows=92,045,113 loops=1)

  • Hash Cond: (pe.event_object_id = ue.event_object_id)
5. 2,614,600.822 5,985,439.827 ↓ 1.0 92,045,113 1

Hash Join (cost=7,042,776.70..19,298,387.43 rows=89,516,883 width=51) (actual time=3,030,317.778..5,985,439.827 rows=92,045,113 loops=1)

  • Hash Cond: (bu.event_object_id = pe.event_object_id)
6. 340,548.587 340,548.587 ↓ 1.0 329,392,904 1

Seq Scan on balance_update bu (cost=0.00..6,905,249.55 rows=329,348,255 width=19) (actual time=10.741..340,548.587 rows=329,392,904 loops=1)

7. 2,320,992.997 3,030,290.418 ↓ 1.0 95,947,139 1

Hash (cost=5,271,322.81..5,271,322.81 rows=91,614,551 width=32) (actual time=3,030,290.418..3,030,290.418 rows=95,947,139 loops=1)

  • Buckets: 131072 Batches: 1024 Memory Usage: 7611kB
8. 709,297.421 709,297.421 ↓ 1.0 95,947,139 1

Index Scan using primary_event_idx_event_time on primary_event pe (cost=0.57..5,271,322.81 rows=91,614,551 width=32) (actual time=37.268..709,297.421 rows=95,947,139 loops=1)

  • Index Cond: (event_time >= '2020-01-01 00:00:00'::timestamp without time zone)
  • Filter: (event_type = '1.3.1'::text)
  • Rows Removed by Filter: 9590380
9. 1,022,245.678 2,090,586.077 ↑ 1.0 333,522,133 1

Hash (cost=11,632,556.67..11,632,556.67 rows=333,529,067 width=12) (actual time=2,090,586.077..2,090,586.077 rows=333,522,133 loops=1)

  • Buckets: 262144 Batches: 4096 Memory Usage: 5848kB
10. 1,068,340.399 1,068,340.399 ↑ 1.0 333,522,133 1

Seq Scan on usage_event ue (cost=0.00..11,632,556.67 rows=333,529,067 width=12) (actual time=6.919..1,068,340.399 rows=333,522,133 loops=1)

11. 2,079,838.238 2,458,087.326 ↓ 1.0 333,522,133 1

Hash (cost=5,667,026.87..5,667,026.87 rows=333,151,987 width=16) (actual time=2,458,087.326..2,458,087.326 rows=333,522,133 loops=1)

  • Buckets: 262144 Batches: 4096 Memory Usage: 6179kB
12. 378,249.088 378,249.088 ↓ 1.0 333,522,133 1

Seq Scan on event_usage_quantity euq (cost=0.00..5,667,026.87 rows=333,151,987 width=16) (actual time=7.630..378,249.088 rows=333,522,133 loops=1)

Planning time : 5.048 ms
Execution time : 14,895,923.759 ms