explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rstm

Settings
# exclusive inclusive rows x rows loops node
1. 68.557 581.347 ↓ 21,948.0 21,948 1

Sort (cost=99,709.57..99,709.57 rows=1 width=296) (actual time=579.901..581.347 rows=21,948 loops=1)

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

CTE clientperformance

3. 6.140 443.966 ↑ 4.0 3,853 1

HashAggregate (cost=98,138.40..98,331.20 rows=15,424 width=64) (actual time=442.916..443.966 rows=3,853 loops=1)

  • Group Key: c.id
4. 8.810 437.826 ↓ 1.2 18,293 1

Nested Loop (cost=45,930.88..97,868.48 rows=15,424 width=27) (actual time=193.080..437.826 rows=18,293 loops=1)

5. 2.573 417.854 ↓ 1.2 11,162 1

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

  • Hash Cond: (p.idgeneralagreement = ga.id)
6. 6.609 415.256 ↓ 1.2 11,162 1

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

7. 42.322 397.485 ↓ 1.2 11,162 1

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

  • Hash Cond: (p.id = pv.idpolicy)
8. 163.930 163.930 ↓ 1.0 415,130 1

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

  • Filter: ((canceldate IS NULL) AND (NOT test))
  • Rows Removed by Filter: 26412
9. 1.727 191.233 ↓ 1.2 11,745 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 863kB
10. 85.351 189.506 ↓ 1.2 11,745 1

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

  • Hash Cond: (pc.idpolicyversion = pv.id)
11. 44.077 44.077 ↓ 1.0 463,762 1

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

12. 1.481 60.078 ↓ 1.2 11,745 1

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

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

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

  • Filter: ((startdate >= '2019-08-04'::date) AND (startdate <= '2019-09-04'::date))
  • Rows Removed by Filter: 452017
14. 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)
15. 0.008 0.025 ↑ 1.0 47 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.017 0.017 ↑ 1.0 47 1

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

17. 11.162 11.162 ↑ 1.0 2 11,162

Index Scan using tblriskpremium_idpolicyversion_index on tblriskpremium rp (cost=0.42..0.57 rows=2 width=13) (actual time=0.001..0.001 rows=2 loops=11,162)

  • Index Cond: (idpolicyversion = pc.idpolicyversion)
18. 11.462 512.790 ↓ 21,948.0 21,948 1

GroupAggregate (cost=1,378.31..1,378.35 rows=1 width=296) (actual time=499.954..512.790 rows=21,948 loops=1)

  • Group Key: ag.id, div.fullname, div.province
19. 12.936 501.328 ↓ 24,859.0 24,859 1

Sort (cost=1,378.31..1,378.31 rows=1 width=104) (actual time=499.944..501.328 rows=24,859 loops=1)

  • Sort Key: ag.id, div.fullname, div.province
  • Sort Method: quicksort Memory: 3872kB
20. 10.744 488.392 ↓ 24,859.0 24,859 1

Hash Join (cost=1,028.69..1,378.30 rows=1 width=104) (actual time=471.982..488.392 rows=24,859 loops=1)

  • Hash Cond: ((ltree2text(subpath(div.path, 0, 1)))::bigint = ag.id)
21. 3.785 461.158 ↓ 121.9 24,861 1

Hash Right Join (cost=514.35..863.31 rows=204 width=90) (actual time=455.482..461.158 rows=24,861 loops=1)

  • Hash Cond: (cp.idagencyunit = div.id)
22. 444.821 444.821 ↑ 4.0 3,853 1

CTE Scan on clientperformance cp (cost=0.00..308.48 rows=15,424 width=56) (actual time=442.919..444.821 rows=3,853 loops=1)

23. 3.863 12.552 ↓ 108.5 22,131 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2073kB
24. 7.301 8.689 ↓ 108.5 22,131 1

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

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

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

  • Index Cond: (nlevel(path) = 2)
26. 5.205 16.490 ↓ 91.7 18,709 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1628kB
27. 10.159 11.285 ↓ 91.7 18,709 1

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

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

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

  • Index Cond: (nlevel(path) = 1)
Planning time : 2.406 ms
Execution time : 584.915 ms