explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 99nz

Settings
# exclusive inclusive rows x rows loops node
1. 78.523 3,901.438 ↓ 10,974.0 21,948 1

Sort (cost=402,596.86..402,596.87 rows=2 width=296) (actual time=3,899.611..3,901.438 rows=21,948 loops=1)

  • Sort Key: ag.fullname, ag.id, div.fullname
  • Sort Method: quicksort Memory: 6313kB
2.          

CTE annualpremiums

3. 133.057 667.889 ↓ 2.1 742,875 1

Unique (cost=91,642.28..97,276.44 rows=357,248 width=40) (actual time=469.538..667.889 rows=742,875 loops=1)

4. 428.688 534.832 ↓ 1.0 751,226 1

Sort (cost=91,642.28..93,520.33 rows=751,222 width=40) (actual time=469.537..534.832 rows=751,226 loops=1)

  • Sort Key: tblriskpremium.idpolicyversion, tblriskpremium.riskname, tblriskpremium.validfrom
  • Sort Method: quicksort Memory: 89099kB
5. 106.144 106.144 ↓ 1.0 751,226 1

Seq Scan on tblriskpremium (cost=0.00..18,327.22 rows=751,222 width=40) (actual time=0.017..106.144 rows=751,226 loops=1)

6.          

CTE premiums

7. 614.578 673.060 ↓ 2.1 742,875 1

HashAggregate (cost=23,961.39..28,426.99 rows=357,248 width=63) (actual time=343.781..673.060 rows=742,875 loops=1)

  • Group Key: tblriskpremium_1.idpolicyversion, tblriskpremium_1.riskname
8. 58.482 58.482 ↓ 1.0 751,226 1

Seq Scan on tblriskpremium tblriskpremium_1 (cost=0.00..18,327.22 rows=751,222 width=36) (actual time=0.013..58.482 rows=751,226 loops=1)

9.          

CTE riskpremium

10. 371.619 3,107.702 ↑ 4.3 710,382 1

Hash Join (cost=89,777.68..107,231.51 rows=3,046,548 width=58) (actual time=2,423.090..3,107.702 rows=710,382 loops=1)

  • Hash Cond: ((ap.idpolicyversion = pv.id) AND (ap.riskname = premiums.riskname))
11. 782.918 782.918 ↓ 2.1 742,875 1

CTE Scan on annualpremiums ap (cost=0.00..7,144.96 rows=357,248 width=58) (actual time=469.542..782.918 rows=742,875 loops=1)

12. 204.535 1,953.165 ↓ 2.1 710,382 1

Hash (cost=84,660.98..84,660.98 rows=341,113 width=81) (actual time=1,953.165..1,953.165 rows=710,382 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 68631kB
13. 239.115 1,748.630 ↓ 2.1 710,382 1

Hash Join (cost=75,682.80..84,660.98 rows=341,113 width=81) (actual time=752.997..1,748.630 rows=710,382 loops=1)

  • Hash Cond: (pv.idpolicy = p.id)
14. 262.259 1,249.199 ↓ 2.1 710,382 1

Hash Join (cost=30,097.17..38,179.92 rows=341,113 width=88) (actual time=492.300..1,249.199 rows=710,382 loops=1)

  • Hash Cond: (premiums.idpolicyversion = pv.id)
15. 838.832 838.832 ↓ 2.1 742,875 1

CTE Scan on premiums (cost=0.00..7,144.96 rows=357,248 width=72) (actual time=343.783..838.832 rows=742,875 loops=1)

16. 67.736 148.108 ↑ 1.0 441,552 1

Hash (cost=24,562.00..24,562.00 rows=442,814 width=16) (actual time=148.108..148.108 rows=441,552 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 24794kB
17. 80.372 80.372 ↑ 1.0 441,552 1

Seq Scan on tblpolicyversion pv (cost=0.00..24,562.00 rows=442,814 width=16) (actual time=0.010..80.372 rows=441,552 loops=1)

  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 22210
18. 67.425 260.316 ↓ 1.0 441,542 1

Hash (cost=40,066.39..40,066.39 rows=441,539 width=9) (actual time=260.316..260.316 rows=441,542 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 24794kB
19. 192.891 192.891 ↓ 1.0 441,542 1

Seq Scan on tblpolicy p (cost=0.00..40,066.39 rows=441,539 width=9) (actual time=0.009..192.891 rows=441,542 loops=1)

20.          

CTE clientperformance

21. 6.733 3,744.893 ↑ 16.2 3,853 1

HashAggregate (cost=166,434.99..167,216.89 rows=62,552 width=64) (actual time=3,743.253..3,744.893 rows=3,853 loops=1)

  • Group Key: c.id
22. 82.326 3,738.160 ↑ 3.5 17,728 1

Hash Join (cost=92,359.29..165,340.33 rows=62,552 width=40) (actual time=3,623.973..3,738.160 rows=17,728 loops=1)

  • Hash Cond: (rp.idpolicyversion = pc.idpolicyversion)
23. 3,263.697 3,263.697 ↑ 4.3 710,382 1

CTE Scan on riskpremium rp (cost=0.00..60,930.96 rows=3,046,548 width=26) (actual time=2,423.094..3,263.697 rows=710,382 loops=1)

24. 1.960 392.137 ↓ 1.2 11,162 1

Hash (cost=92,240.27..92,240.27 rows=9,522 width=38) (actual time=392.137..392.137 rows=11,162 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 893kB
25. 2.688 390.177 ↓ 1.2 11,162 1

Hash Join (cost=45,930.46..92,240.27 rows=9,522 width=38) (actual time=179.660..390.177 rows=11,162 loops=1)

  • Hash Cond: (p_1.idgeneralagreement = ga.id)
26. 6.419 387.462 ↓ 1.2 11,162 1

Nested Loop (cost=45,927.40..92,209.96 rows=9,522 width=58) (actual time=179.625..387.462 rows=11,162 loops=1)

27. 36.510 369.881 ↓ 1.2 11,162 1

Hash Join (cost=45,926.98..87,642.80 rows=9,522 width=50) (actual time=179.608..369.881 rows=11,162 loops=1)

  • Hash Cond: (p_1.id = pv_1.idpolicy)
28. 155.374 155.374 ↓ 1.0 415,130 1

Seq Scan on tblpolicy p_1 (cost=0.00..40,066.39 rows=414,456 width=45) (actual time=0.005..155.374 rows=415,130 loops=1)

  • Filter: ((canceldate IS NULL) AND (NOT test))
  • Rows Removed by Filter: 26412
29. 1.587 177.997 ↓ 1.2 11,745 1

Hash (cost=45,800.18..45,800.18 rows=10,144 width=32) (actual time=177.997..177.997 rows=11,745 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 863kB
30. 79.114 176.410 ↓ 1.2 11,745 1

Hash Join (cost=25,848.20..45,800.18 rows=10,144 width=32) (actual time=59.015..176.410 rows=11,745 loops=1)

  • Hash Cond: (pc.idpolicyversion = pv_1.id)
31. 41.224 41.224 ↓ 1.0 463,762 1

Seq Scan on tblpolicyclient pc (cost=0.00..18,734.60 rows=463,760 width=16) (actual time=0.004..41.224 rows=463,762 loops=1)

32. 1.446 56.072 ↓ 1.2 11,745 1

Hash (cost=25,721.40..25,721.40 rows=10,144 width=16) (actual time=56.072..56.072 rows=11,745 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 679kB
33. 54.626 54.626 ↓ 1.2 11,745 1

Seq Scan on tblpolicyversion pv_1 (cost=0.00..25,721.40 rows=10,144 width=16) (actual time=0.005..54.626 rows=11,745 loops=1)

  • Filter: ((startdate >= '2019-08-04'::date) AND (startdate <= '2019-09-04'::date))
  • Rows Removed by Filter: 452017
34. 11.162 11.162 ↑ 1.0 1 11,162

Index Scan using tblclient_pkey on tblclient c (cost=0.42..0.48 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=11,162)

  • Index Cond: (id = pc.idclient)
35. 0.008 0.027 ↑ 1.0 47 1

Hash (cost=2.47..2.47 rows=47 width=16) (actual time=0.027..0.027 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
36. 0.019 0.019 ↑ 1.0 47 1

Seq Scan on tblgeneralagreement ga (cost=0.00..2.47 rows=47 width=16) (actual time=0.015..0.019 rows=47 loops=1)

37. 14.057 3,822.915 ↓ 10,974.0 21,948 1

GroupAggregate (cost=2,444.94..2,445.03 rows=2 width=296) (actual time=3,805.951..3,822.915 rows=21,948 loops=1)

  • Group Key: ag.id, div.fullname, div.province
38. 16.106 3,808.858 ↓ 12,429.5 24,859 1

Sort (cost=2,444.94..2,444.94 rows=2 width=104) (actual time=3,805.936..3,808.858 rows=24,859 loops=1)

  • Sort Key: ag.id, div.fullname, div.province
  • Sort Method: quicksort Memory: 3872kB
39. 13.849 3,792.752 ↓ 12,429.5 24,859 1

Hash Join (cost=1,028.69..2,444.93 rows=2 width=104) (actual time=3,771.564..3,792.752 rows=24,859 loops=1)

  • Hash Cond: ((ltree2text(subpath(div.path, 0, 1)))::bigint = ag.id)
40. 4.764 3,765.610 ↓ 79.7 24,861 1

Hash Right Join (cost=514.35..1,929.61 rows=312 width=90) (actual time=3,758.260..3,765.610 rows=24,861 loops=1)

  • Hash Cond: (cp.idagencyunit = div.id)
41. 3,745.856 3,745.856 ↑ 16.2 3,853 1

CTE Scan on clientperformance cp (cost=0.00..1,251.04 rows=62,552 width=56) (actual time=3,743.256..3,745.856 rows=3,853 loops=1)

42. 4.925 14.990 ↓ 108.5 22,131 1

Hash (cost=511.80..511.80 rows=204 width=50) (actual time=14.990..14.990 rows=22,131 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2073kB
43. 8.724 10.065 ↓ 108.5 22,131 1

Bitmap Heap Scan on tblagencyunit div (cost=5.87..511.80 rows=204 width=50) (actual time=1.453..10.065 rows=22,131 loops=1)

  • Recheck Cond: (nlevel(path) = 2)
  • Filter: (NOT test)
  • Rows Removed by Filter: 14
  • Heap Blocks: exact=981
44. 1.341 1.341 ↓ 108.6 22,145 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..5.82 rows=204 width=0) (actual time=1.341..1.341 rows=22,145 loops=1)

  • Index Cond: (nlevel(path) = 2)
45. 4.112 13.293 ↓ 91.7 18,709 1

Hash (cost=511.80..511.80 rows=204 width=32) (actual time=13.293..13.293 rows=18,709 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1628kB
46. 8.498 9.181 ↓ 91.7 18,709 1

Bitmap Heap Scan on tblagencyunit ag (cost=5.87..511.80 rows=204 width=32) (actual time=0.760..9.181 rows=18,709 loops=1)

  • Recheck Cond: (nlevel(path) = 1)
  • Filter: (NOT test)
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=821
47. 0.683 0.683 ↓ 91.8 18,717 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..5.82 rows=204 width=0) (actual time=0.683..0.683 rows=18,717 loops=1)

  • Index Cond: (nlevel(path) = 1)
Planning time : 3.986 ms
Execution time : 3,944.090 ms