explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dqET : Optimization for: plan #sWw6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 3,927.125 ↑ 1.0 10 1

Limit (cost=286,305.95..286,305.98 rows=10 width=797) (actual time=3,927.122..3,927.125 rows=10 loops=1)

2. 89.616 3,927.123 ↑ 1,607.7 10 1

Sort (cost=286,305.95..286,346.14 rows=16,077 width=797) (actual time=3,927.121..3,927.123 rows=10 loops=1)

  • Sort Key: oo.payment_date
  • Sort Method: top-N heapsort Memory: 30kB
3. 1,599.417 3,837.507 ↓ 6.8 109,996 1

Hash Join (cost=102,975.58..285,958.53 rows=16,077 width=797) (actual time=1,140.749..3,837.507 rows=109,996 loops=1)

  • Hash Cond: (p.firm_id = f.id)
4. 47.293 2,017.881 ↓ 6.8 109,996 1

Hash Join (cost=102,965.46..146,741.69 rows=16,077 width=775) (actual time=1,077.933..2,017.881 rows=109,996 loops=1)

  • Hash Cond: (va.program_id = p.id)
5. 91.701 1,970.504 ↓ 6.8 109,996 1

Nested Loop (cost=102,961.24..146,516.40 rows=16,077 width=767) (actual time=1,077.838..1,970.504 rows=109,996 loops=1)

6. 219.023 1,658.811 ↓ 6.8 109,996 1

Hash Join (cost=102,960.81..133,746.16 rows=16,077 width=742) (actual time=1,077.802..1,658.811 rows=109,996 loops=1)

  • Hash Cond: (oo.id = i.order_offer_id)
7. 176.170 1,143.306 ↓ 6.2 112,890 1

Hash Join (cost=73,488.60..99,221.28 rows=18,302 width=655) (actual time=781.151..1,143.306 rows=112,890 loops=1)

  • Hash Cond: (oo.user_id = u.id)
8. 125.520 489.782 ↓ 2.6 112,890 1

Hash Join (cost=34,887.38..52,615.16 rows=43,169 width=569) (actual time=303.740..489.782 rows=112,890 loops=1)

  • Hash Cond: (t.order_offer_id = oo.id)
9. 60.817 60.817 ↓ 1.0 121,622 1

Seq Scan on transfers t (cost=0.00..7,832.93 rows=120,842 width=24) (actual time=0.024..60.817 rows=121,622 loops=1)

  • Filter: (order_offer_id IS NOT NULL)
  • Rows Removed by Filter: 182971
10. 104.819 303.445 ↑ 1.0 107,918 1

Hash (cost=25,945.78..25,945.78 rows=107,968 width=545) (actual time=303.445..303.445 rows=107,918 loops=1)

  • Buckets: 8192 Batches: 16 Memory Usage: 3856kB
11. 47.840 198.626 ↑ 1.0 107,918 1

Hash Left Join (cost=442.01..25,945.78 rows=107,968 width=545) (actual time=3.348..198.626 rows=107,918 loops=1)

  • Hash Cond: (oo.id = p24.order_offer_id)
12. 147.479 147.479 ↑ 1.0 107,918 1

Seq Scan on orders_offers oo (cost=0.00..25,004.84 rows=107,968 width=528) (actual time=0.016..147.479 rows=107,918 loops=1)

  • Filter: (status = ANY ('{bought,delivered}'::t_order_offer_status[]))
  • Rows Removed by Filter: 11989
13. 1.605 3.307 ↑ 1.0 10,445 1

Hash (cost=311.45..311.45 rows=10,445 width=25) (actual time=3.307..3.307 rows=10,445 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 773kB
14. 1.702 1.702 ↑ 1.0 10,445 1

Seq Scan on przelewy24_transactions p24 (cost=0.00..311.45 rows=10,445 width=25) (actual time=0.004..1.702 rows=10,445 loops=1)

15. 39.059 477.354 ↓ 1.2 115,988 1

Hash (cost=36,044.13..36,044.13 rows=97,687 width=86) (actual time=477.354..477.354 rows=115,988 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 3224kB
16. 99.979 438.295 ↓ 1.2 115,988 1

Hash Right Join (cost=25,755.70..36,044.13 rows=97,687 width=86) (actual time=305.391..438.295 rows=115,988 loops=1)

  • Hash Cond: (a.id = a2u.address_id)
17. 33.082 33.082 ↓ 1.0 200,889 1

Seq Scan on addresses a (cost=0.00..4,273.41 rows=200,841 width=37) (actual time=0.010..33.082 rows=200,889 loops=1)

18. 30.578 305.234 ↓ 1.2 115,988 1

Hash (cost=23,389.61..23,389.61 rows=97,687 width=65) (actual time=305.234..305.234 rows=115,988 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 4075kB
19. 123.856 274.656 ↓ 1.2 115,988 1

Hash Right Join (cost=12,316.25..23,389.61 rows=97,687 width=65) (actual time=125.636..274.656 rows=115,988 loops=1)

  • Hash Cond: (a2u.user_id = u.id)
  • Filter: ((a2u.is_mailing_address IS TRUE) OR (a2u.is_mailing_address IS NULL))
  • Rows Removed by Filter: 115555
20. 25.352 25.352 ↑ 1.0 200,642 1

Seq Scan on a2u (cost=0.00..3,483.31 rows=200,731 width=17) (actual time=0.004..25.352 rows=200,642 loops=1)

21. 62.097 125.448 ↑ 1.0 230,284 1

Hash (cost=6,960.11..6,960.11 rows=230,411 width=57) (actual time=125.448..125.448 rows=230,284 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2635kB
22. 63.351 63.351 ↑ 1.0 230,284 1

Seq Scan on users u (cost=0.00..6,960.11 rows=230,411 width=57) (actual time=0.028..63.351 rows=230,284 loops=1)

23. 30.658 296.482 ↓ 1.0 106,685 1

Hash (cost=26,509.60..26,509.60 rows=105,329 width=103) (actual time=296.482..296.482 rows=106,685 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 2569kB
24. 47.947 265.824 ↓ 1.0 106,685 1

Merge Join (cost=14,454.50..26,509.60 rows=105,329 width=103) (actual time=100.498..265.824 rows=106,685 loops=1)

  • Merge Cond: (invoices_details.invoice_id = i.id)
25. 80.458 195.814 ↓ 1.0 106,685 1

GroupAggregate (cost=14,454.08..18,201.63 rows=105,329 width=32) (actual time=100.480..195.814 rows=106,685 loops=1)

  • Group Key: invoices_details.invoice_id
26. 89.657 115.356 ↑ 1.0 108,381 1

Sort (cost=14,454.08..14,725.03 rows=108,381 width=32) (actual time=100.460..115.356 rows=108,381 loops=1)

  • Sort Key: invoices_details.invoice_id
  • Sort Method: external sort Disk: 4448kB
27. 25.699 25.699 ↑ 1.0 108,381 1

Seq Scan on invoices_details (cost=0.00..2,796.81 rows=108,381 width=32) (actual time=0.008..25.699 rows=108,381 loops=1)

28. 22.063 22.063 ↑ 1.0 106,685 1

Index Scan using invoices_pkey on invoices i (cost=0.42..5,671.35 rows=106,685 width=39) (actual time=0.013..22.063 rows=106,685 loops=1)

29. 219.992 219.992 ↑ 1.0 1 109,996

Index Scan using virtual_accounts_balance_id_key on virtual_accounts va (cost=0.42..0.78 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=109,996)

  • Index Cond: (balance_id = t.source_balance)
30. 0.040 0.084 ↑ 1.0 99 1

Hash (cost=2.99..2.99 rows=99 width=24) (actual time=0.084..0.084 rows=99 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
31. 0.044 0.044 ↑ 1.0 99 1

Seq Scan on programs p (cost=0.00..2.99 rows=99 width=24) (actual time=0.006..0.044 rows=99 loops=1)

32. 0.123 0.217 ↓ 1.0 273 1

Hash (cost=6.72..6.72 rows=272 width=30) (actual time=0.217..0.217 rows=273 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
33. 0.094 0.094 ↓ 1.0 273 1

Seq Scan on firms f (cost=0.00..6.72 rows=272 width=30) (actual time=0.013..0.094 rows=273 loops=1)

34.          

SubPlan (for Hash Join)

35. 219.992 219.992 ↑ 1.0 1 109,996

Index Scan using user_firm_properties_user_id_firm_id_key on user_firm_properties ufp (cost=0.28..8.30 rows=1 width=54) (actual time=0.002..0.002 rows=1 loops=109,996)

  • Index Cond: ((user_id = u.id) AND (firm_id = f.id))
Planning time : 6.667 ms
Execution time : 3,928.432 ms