explain.depesz.com

A tool for finding a real cause for slow queries.

Result: KKe

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.014 323,694.308 ↑ 1.0 10 1

Hash Left Join (cost=12,463,520.78..12,463,521.03 rows=10 width=49) (actual time=323,694.301..323,694.308 rows=10 loops=1)

  • Hash Cond: (sample.invno = rejections_0_14_days.invno)
2.          

CTE sample

3. 0.006 0.038 ↑ 1.0 10 1

Limit (cost=0.00..0.45 rows=10 width=8) (actual time=0.012..0.038 rows=10 loops=1)

4. 0.032 0.032 ↑ 3,520,414.1 10 1

Seq Scan on invoice (cost=0.00..1,594,783.41 rows=35,204,141 width=8) (actual time=0.010..0.032 rows=10 loops=1)

5.          

CTE sample_cids

6. 0.031 80.708 ↑ 1.0 10 1

HashAggregate (cost=206.93..207.03 rows=10 width=8) (actual time=80.700..80.708 rows=10 loops=1)

7. 0.038 80.677 ↑ 1.0 10 1

Nested Loop (cost=0.00..206.90 rows=10 width=8) (actual time=0.046..80.677 rows=10 loops=1)

8. 0.032 31.689 ↑ 1.0 10 1

Nested Loop (cost=0.00..171.89 rows=10 width=14) (actual time=0.019..31.689 rows=10 loops=1)

9. 0.037 0.037 ↑ 1.0 10 1

CTE Scan on sample (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.037 rows=10 loops=1)

10. 31.620 31.620 ↑ 1.0 1 10

Index Scan using invoice_invno_index on invoice (cost=0.00..17.16 rows=1 width=22) (actual time=3.162..3.162 rows=1 loops=10)

  • Index Cond: (invno = sample.invno)
11. 48.950 48.950 ↑ 1.0 1 10

Index Scan using customer_pno_index on customer (cost=0.00..3.49 rows=1 width=24) (actual time=4.894..4.895 rows=1 loops=10)

  • Index Cond: (pno = public.invoice.pno)
12.          

CTE old_rejections

13. 690.781 7,096.124 ↑ 2.3 61 1

Hash Join (cost=1,007.03..369,486.94 rows=142 width=51) (actual time=381.620..7,096.124 rows=61 loops=1)

  • Hash Cond: (rejection.pno = public.customer.pno)
14. 6,261.890 6,261.890 ↓ 1.0 7,577,476 1

Seq Scan on rejection (cost=0.00..340,821.19 rows=7,372,677 width=58) (actual time=18.155..6,261.890 rows=7,577,476 loops=1)

  • Filter: (reason IS NOT NULL)
15. 0.030 143.453 ↑ 21.5 11 1

Hash (cost=1,004.08..1,004.08 rows=236 width=24) (actual time=143.453..143.453 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
16. 0.036 143.423 ↑ 21.5 11 1

Nested Loop (cost=0.00..1,004.08 rows=236 width=24) (actual time=85.957..143.423 rows=11 loops=1)

17. 80.727 80.727 ↑ 1.0 10 1

CTE Scan on sample_cids (cost=0.00..0.20 rows=10 width=8) (actual time=80.702..80.727 rows=10 loops=1)

18. 62.660 62.660 ↑ 24.0 1 10

Index Scan using customer_cid_idx on customer (cost=0.00..100.09 rows=24 width=24) (actual time=6.265..6.266 rows=1 loops=10)

  • Index Cond: (cid = sample_cids.cid)
19.          

CTE payments

20. 5,336.726 306,680.168 ↑ 1.1 27,670,712 1

Group (cost=11,148,902.33..11,375,432.30 rows=30,203,996 width=16) (actual time=267,071.189..306,680.168 rows=27,670,712 loops=1)

21. 88,724.779 301,343.442 ↑ 1.0 30,179,254 1

Sort (cost=11,148,902.33..11,224,412.32 rows=30,203,996 width=16) (actual time=267,071.185..301,343.442 rows=30,179,254 loops=1)

  • Sort Key: public.customer.cid, public.inv_paid_hist.date
  • Sort Method: external merge Disk: 760456kB
22. 73,886.514 212,618.663 ↑ 1.0 30,179,254 1

Hash Join (cost=2,742,721.08..6,880,138.71 rows=30,203,996 width=16) (actual time=46,615.192..212,618.663 rows=30,179,254 loops=1)

  • Hash Cond: (public.invoice.pno = public.customer.pno)
23. 66,576.975 126,619.982 ↑ 1.0 30,179,122 1

Hash Join (cost=2,241,110.17..5,122,141.91 rows=30,203,996 width=22) (actual time=34,498.868..126,619.982 rows=30,179,122 loops=1)

  • Hash Cond: (public.inv_paid_hist.invno = public.invoice.invno)
24. 25,552.430 25,552.430 ↑ 1.0 30,179,122 1

Seq Scan on inv_paid_hist (cost=0.00..680,819.96 rows=30,203,996 width=16) (actual time=4.545..25,552.430 rows=30,179,122 loops=1)

25. 8,183.324 34,490.577 ↑ 1.0 34,848,073 1

Hash (cost=1,594,783.41..1,594,783.41 rows=35,204,141 width=22) (actual time=34,490.577..34,490.577 rows=34,848,073 loops=1)

  • Buckets: 1048576 Batches: 4 Memory Usage: 463014kB
26. 26,307.253 26,307.253 ↑ 1.0 34,848,073 1

Seq Scan on invoice (cost=0.00..1,594,783.41 rows=35,204,141 width=22) (actual time=0.010..26,307.253 rows=34,848,073 loops=1)

27. 3,379.187 12,112.167 ↓ 1.0 12,284,213 1

Hash (cost=276,568.96..276,568.96 rows=12,257,596 width=24) (actual time=12,112.167..12,112.167 rows=12,284,213 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 339167kB
28. 8,732.980 8,732.980 ↓ 1.0 12,284,213 1

Seq Scan on customer (cost=0.00..276,568.96 rows=12,257,596 width=24) (actual time=0.004..8,732.980 rows=12,284,213 loops=1)

29.          

CTE paid_since_rejected

30. 0.003 316,507.619 ↓ 0.0 0 1

HashAggregate (cost=718,182.35..718,182.36 rows=1 width=8) (actual time=316,507.619..316,507.619 rows=0 loops=1)

31. 0.082 316,507.616 ↓ 0.0 0 1

Nested Loop (cost=207.03..718,182.35 rows=1 width=8) (actual time=316,507.616..316,507.616 rows=0 loops=1)

  • Join Filter: ((payments.pay_date > o.rej_date) AND (public.customer.cid = o.cid) AND ((public.invoice.create_date - o.rej_date) < '14 days'::interval))
32. 0.006 0.006 ↑ 2.3 61 1

CTE Scan on old_rejections o (cost=0.00..2.84 rows=142 width=16) (actual time=0.001..0.006 rows=61 loops=1)

33. 0.051 316,507.528 ↑ 17.6 11 61

Materialize (cost=207.03..717,560.16 rows=194 width=40) (actual time=4,381.125..5,188.648 rows=11 loops=61)

34. 2,239.674 316,507.477 ↑ 17.6 11 1

Hash Join (cost=207.03..717,559.19 rows=194 width=40) (actual time=267,248.614..316,507.477 rows=11 loops=1)

  • Hash Cond: (payments.cid = public.customer.cid)
  • Join Filter: (payments.pay_date < public.invoice.create_date)
35. 314,267.563 314,267.563 ↑ 1.1 27,670,712 1

CTE Scan on payments (cost=0.00..604,079.92 rows=30,203,996 width=16) (actual time=267,071.192..314,267.563 rows=27,670,712 loops=1)

36. 0.003 0.240 ↑ 1.0 10 1

Hash (cost=206.90..206.90 rows=10 width=24) (actual time=0.240..0.240 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
37. 0.006 0.237 ↑ 1.0 10 1

Nested Loop (cost=0.00..206.90 rows=10 width=24) (actual time=0.061..0.237 rows=10 loops=1)

38. 0.008 0.091 ↑ 1.0 10 1

Nested Loop (cost=0.00..171.89 rows=10 width=30) (actual time=0.035..0.091 rows=10 loops=1)

39. 0.003 0.003 ↑ 1.0 10 1

CTE Scan on sample (cost=0.00..0.20 rows=10 width=8) (actual time=0.002..0.003 rows=10 loops=1)

40. 0.080 0.080 ↑ 1.0 1 10

Index Scan using invoice_invno_index on invoice (cost=0.00..17.16 rows=1 width=30) (actual time=0.008..0.008 rows=1 loops=10)

  • Index Cond: (invno = sample.invno)
41. 0.140 0.140 ↑ 1.0 1 10

Index Scan using customer_pno_index on customer (cost=0.00..3.49 rows=1 width=24) (actual time=0.013..0.014 rows=1 loops=10)

  • Index Cond: (pno = public.invoice.pno)
42.          

CTE rejections_0_14_days

43. 0.011 323,694.274 ↓ 2.0 2 1

HashAggregate (cost=211.66..211.67 rows=1 width=64) (actual time=323,694.274..323,694.274 rows=2 loops=1)

44. 0.030 323,694.263 ↓ 6.0 6 1

WindowAgg (cost=211.61..211.65 rows=1 width=64) (actual time=323,694.256..323,694.263 rows=6 loops=1)

45. 0.011 323,694.233 ↓ 6.0 6 1

Sort (cost=211.61..211.62 rows=1 width=64) (actual time=323,694.232..323,694.233 rows=6 loops=1)

  • Sort Key: public.invoice.invno, o.rej_date
  • Sort Method: quicksort Memory: 25kB
46. 0.002 323,694.222 ↓ 6.0 6 1

Nested Loop Left Join (cost=4.62..211.60 rows=1 width=64) (actual time=323,694.102..323,694.222 rows=6 loops=1)

  • Join Filter: (sample.invno = ps.invno)
47. 0.070 7,186.600 ↓ 6.0 6 1

Hash Join (cost=4.62..211.57 rows=1 width=64) (actual time=7,186.481..7,186.600 rows=6 loops=1)

  • Hash Cond: (public.customer.cid = o.cid)
  • Join Filter: ((o.rej_date < public.invoice.create_date) AND ((public.invoice.create_date - o.rej_date) < '14 days'::interval))
48. 0.020 89.947 ↑ 1.0 10 1

Nested Loop (cost=0.00..206.90 rows=10 width=32) (actual time=89.713..89.947 rows=10 loops=1)

49. 0.022 38.787 ↑ 1.0 10 1

Nested Loop (cost=0.00..171.89 rows=10 width=38) (actual time=38.690..38.787 rows=10 loops=1)

50. 0.005 0.005 ↑ 1.0 10 1

CTE Scan on sample (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.005 rows=10 loops=1)

51. 38.760 38.760 ↑ 1.0 1 10

Index Scan using invoice_invno_index on invoice (cost=0.00..17.16 rows=1 width=30) (actual time=3.876..3.876 rows=1 loops=10)

  • Index Cond: (invno = sample.invno)
52. 51.140 51.140 ↑ 1.0 1 10

Index Scan using customer_pno_index on customer (cost=0.00..3.49 rows=1 width=24) (actual time=5.114..5.114 rows=1 loops=10)

  • Index Cond: (pno = public.invoice.pno)
53. 0.107 7,096.583 ↑ 2.3 61 1

Hash (cost=2.84..2.84 rows=142 width=48) (actual time=7,096.583..7,096.583 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
54. 7,096.476 7,096.476 ↑ 2.3 61 1

CTE Scan on old_rejections o (cost=0.00..2.84 rows=142 width=48) (actual time=381.626..7,096.476 rows=61 loops=1)

55. 316,507.620 316,507.620 ↓ 0.0 0 6

CTE Scan on paid_since_rejected ps (cost=0.00..0.02 rows=1 width=8) (actual time=52,751.270..52,751.270 rows=0 loops=6)

56. 0.015 0.015 ↑ 1.0 10 1

CTE Scan on sample (cost=0.00..0.20 rows=10 width=8) (actual time=0.014..0.015 rows=10 loops=1)

57. 0.001 323,694.279 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=49) (actual time=323,694.279..323,694.279 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
58. 323,694.278 323,694.278 ↓ 2.0 2 1

CTE Scan on rejections_0_14_days (cost=0.00..0.02 rows=1 width=49) (actual time=323,694.276..323,694.278 rows=2 loops=1)