explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0M2P : Optimization for: plan #XKN1

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 13,485.500 681,000.756 ↑ 229.9 300,373 1

Hash Left Join (cost=15,671,563.01..29,533,747.19 rows=69,046,720 width=1,622) (actual time=340,163.836..681,000.756 rows=300,373 loops=1)

  • Hash Cond: (t.id = tips.fresha_pay_transaction_id)
  • Filter: (((t.provider_id = 9837) AND (t.status = ANY ('{2,3}'::integer[])) AND (s.id IS NOT NULL)) OR (orig_s.id IS NOT NULL) OR (tips_sale.id IS NOT NULL))
  • Rows Removed by Filter: 68721994
2. 29,765.586 667,513.924 ↑ 1.0 69,022,367 1

Hash Left Join (cost=15,671,545.96..27,893,870.52 rows=69,046,720 width=138) (actual time=246,276.077..667,513.924 rows=69,022,367 loops=1)

  • Hash Cond: (s.original_sale_id = orig_s.id)
3. 161,646.720 573,391.027 ↑ 1.0 69,022,367 1

Hash Left Join (cost=10,461,234.66..19,383,703.58 rows=69,046,720 width=138) (actual time=181,916.188..573,391.027 rows=69,022,367 loops=1)

  • Hash Cond: (t.sale_id = s.id)
4. 77,313.110 271,605.124 ↑ 1.0 69,022,367 1

Hash Join (cost=2,439,606.44..8,573,298.71 rows=69,046,720 width=74) (actual time=41,057.202..271,605.124 rows=69,022,367 loops=1)

  • Hash Cond: (t.provider_id = p.id)
5. 116,934.750 193,919.840 ↑ 1.0 69,022,367 1

Hash Left Join (cost=2,402,459.20..6,869,453.61 rows=69,046,720 width=58) (actual time=40,666.415..193,919.840 rows=69,022,367 loops=1)

  • Hash Cond: (t.id = td.id)
6. 47,571.617 47,571.617 ↑ 1.0 69,022,367 1

Seq Scan on transactions t (cost=0.00..2,600,573.68 rows=69,022,368 width=52) (actual time=1,910.085..47,571.617 rows=69,022,367 loops=1)

7. 15,969.838 29,413.473 ↑ 1.0 69,022,350 1

Hash (cost=1,202,232.20..1,202,232.20 rows=69,046,720 width=10) (actual time=29,413.473..29,413.473 rows=69,022,350 loops=1)

  • Buckets: 131072 Batches: 2048 Memory Usage: 2502kB
8. 13,443.635 13,443.635 ↑ 1.0 69,022,350 1

Seq Scan on transaction_details td (cost=0.00..1,202,232.20 rows=69,046,720 width=10) (actual time=0.012..13,443.635 rows=69,022,350 loops=1)

9. 68.165 372.174 ↑ 1.0 344,944 1

Hash (cost=30,813.44..30,813.44 rows=344,944 width=20) (actual time=372.174..372.174 rows=344,944 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2788kB
10. 304.009 304.009 ↑ 1.0 344,944 1

Seq Scan on providers p (cost=0.00..30,813.44 rows=344,944 width=20) (actual time=0.631..304.009 rows=344,944 loops=1)

11. 47,331.194 140,139.183 ↑ 1.0 72,395,415 1

Hash (cost=6,255,891.28..6,255,891.28 rows=72,907,835 width=68) (actual time=140,139.183..140,139.183 rows=72,395,415 loops=1)

  • Buckets: 65536 Batches: 4096 Memory Usage: 2225kB
12. 92,807.989 92,807.989 ↑ 1.0 72,395,415 1

Seq Scan on sales s (cost=0.00..6,255,891.28 rows=72,907,835 width=68) (actual time=1.545..92,807.989 rows=72,395,415 loops=1)

  • Filter: (NOT voided)
  • Rows Removed by Filter: 480660
13. 13,697.949 64,357.311 ↑ 1.0 72,876,075 1

Hash (cost=4,006,542.70..4,006,542.70 rows=73,372,528 width=4) (actual time=64,357.311..64,357.311 rows=72,876,075 loops=1)

  • Buckets: 131072 Batches: 1024 Memory Usage: 3522kB
14. 50,659.362 50,659.362 ↑ 1.0 72,876,075 1

Index Only Scan using sales_pkey on sales orig_s (cost=0.57..4,006,542.70 rows=73,372,528 width=4) (actual time=0.034..50,659.362 rows=72,876,075 loops=1)

  • Heap Fetches: 6369
15. 0.001 1.332 ↓ 0.0 0 1

Hash (cost=17.04..17.04 rows=1 width=68) (actual time=1.331..1.332 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.001 1.331 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.00..17.04 rows=1 width=68) (actual time=1.331..1.331 rows=0 loops=1)

17. 1.330 1.330 ↓ 0.0 0 1

Index Scan using index_tips_on_channel on tips (cost=0.43..8.45 rows=1 width=8) (actual time=1.330..1.330 rows=0 loops=1)

  • Index Cond: (channel = 2)
  • Filter: paid
18. 0.000 0.000 ↓ 0.0 0

Index Scan using sales_pkey on sales tips_sale (cost=0.57..8.59 rows=1 width=68) (never executed)

  • Index Cond: (id = tips.sale_id)