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 323694.308 ↑ 1.0 10 1

Hash Left Join (cost=12463520.78..12463521.03 rows=10 width=49) (actual time=323694.301..323694.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 ↑ 3520414.1 10 1

Seq Scan on invoice (cost=0.00..1594783.41 rows=35204141 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 7096.124 ↑ 2.3 61 1

Hash Join (cost=1007.03..369486.94 rows=142 width=51) (actual time=381.620..7096.124 rows=61 loops=1)

  • Hash Cond: (rejection.pno = public.customer.pno)
14. 6261.890 6261.890 ↓ 1.0 7577476 1

Seq Scan on rejection (cost=0.00..340821.19 rows=7372677 width=58) (actual time=18.155..6261.890 rows=7577476 loops=1)

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

Hash (cost=1004.08..1004.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..1004.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. 5336.726 306680.168 ↑ 1.1 27670712 1

Group (cost=11148902.33..11375432.30 rows=30203996 width=16) (actual time=267071.189..306680.168 rows=27670712 loops=1)

21. 88724.779 301343.442 ↑ 1.0 30179254 1

Sort (cost=11148902.33..11224412.32 rows=30203996 width=16) (actual time=267071.185..301343.442 rows=30179254 loops=1)

  • Sort Key: public.customer.cid, public.inv_paid_hist.date
  • Sort Method: external merge Disk: 760456kB
22. 73886.514 212618.663 ↑ 1.0 30179254 1

Hash Join (cost=2742721.08..6880138.71 rows=30203996 width=16) (actual time=46615.192..212618.663 rows=30179254 loops=1)

  • Hash Cond: (public.invoice.pno = public.customer.pno)
23. 66576.975 126619.982 ↑ 1.0 30179122 1

Hash Join (cost=2241110.17..5122141.91 rows=30203996 width=22) (actual time=34498.868..126619.982 rows=30179122 loops=1)

  • Hash Cond: (public.inv_paid_hist.invno = public.invoice.invno)
24. 25552.430 25552.430 ↑ 1.0 30179122 1

Seq Scan on inv_paid_hist (cost=0.00..680819.96 rows=30203996 width=16) (actual time=4.545..25552.430 rows=30179122 loops=1)

25. 8183.324 34490.577 ↑ 1.0 34848073 1

Hash (cost=1594783.41..1594783.41 rows=35204141 width=22) (actual time=34490.577..34490.577 rows=34848073 loops=1)

  • Buckets: 1048576 Batches: 4 Memory Usage: 463014kB
26. 26307.253 26307.253 ↑ 1.0 34848073 1

Seq Scan on invoice (cost=0.00..1594783.41 rows=35204141 width=22) (actual time=0.010..26307.253 rows=34848073 loops=1)

27. 3379.187 12112.167 ↓ 1.0 12284213 1

Hash (cost=276568.96..276568.96 rows=12257596 width=24) (actual time=12112.167..12112.167 rows=12284213 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 339167kB
28. 8732.980 8732.980 ↓ 1.0 12284213 1

Seq Scan on customer (cost=0.00..276568.96 rows=12257596 width=24) (actual time=0.004..8732.980 rows=12284213 loops=1)

29.          

CTE paid_since_rejected

30. 0.003 316507.619 ↓ 0.0 0 1

HashAggregate (cost=718182.35..718182.36 rows=1 width=8) (actual time=316507.619..316507.619 rows=0 loops=1)

31. 0.082 316507.616 ↓ 0.0 0 1

Nested Loop (cost=207.03..718182.35 rows=1 width=8) (actual time=316507.616..316507.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 316507.528 ↑ 17.6 11 61

Materialize (cost=207.03..717560.16 rows=194 width=40) (actual time=4381.125..5188.648 rows=11 loops=61)

34. 2239.674 316507.477 ↑ 17.6 11 1

Hash Join (cost=207.03..717559.19 rows=194 width=40) (actual time=267248.614..316507.477 rows=11 loops=1)

  • Hash Cond: (payments.cid = public.customer.cid)
  • Join Filter: (payments.pay_date < public.invoice.create_date)
35. 314267.563 314267.563 ↑ 1.1 27670712 1

CTE Scan on payments (cost=0.00..604079.92 rows=30203996 width=16) (actual time=267071.192..314267.563 rows=27670712 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 323694.274 ↓ 2.0 2 1

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

44. 0.030 323694.263 ↓ 6.0 6 1

WindowAgg (cost=211.61..211.65 rows=1 width=64) (actual time=323694.256..323694.263 rows=6 loops=1)

45. 0.011 323694.233 ↓ 6.0 6 1

Sort (cost=211.61..211.62 rows=1 width=64) (actual time=323694.232..323694.233 rows=6 loops=1)

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

Nested Loop Left Join (cost=4.62..211.60 rows=1 width=64) (actual time=323694.102..323694.222 rows=6 loops=1)

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

Hash Join (cost=4.62..211.57 rows=1 width=64) (actual time=7186.481..7186.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 7096.583 ↑ 2.3 61 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
54. 7096.476 7096.476 ↑ 2.3 61 1

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

55. 316507.620 316507.620 ↓ 0.0 0 6

CTE Scan on paid_since_rejected ps (cost=0.00..0.02 rows=1 width=8) (actual time=52751.270..52751.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 323694.279 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=49) (actual time=323694.279..323694.279 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
58. 323694.278 323694.278 ↓ 2.0 2 1

CTE Scan on rejections_0_14_days (cost=0.00..0.02 rows=1 width=49) (actual time=323694.276..323694.278 rows=2 loops=1)