explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BVZ5

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 66,275.878 ↑ 3.0 10 1

Unique (cost=5,917,872.81..5,917,875.89 rows=30 width=726) (actual time=66,275.869..66,275.878 rows=10 loops=1)

2.          

CTE cte_base_3848145b2bf530

3. 2,160.944 19,529.123 ↑ 1.0 13,161,010 1

Unique (cost=0.56..2,037,947.61 rows=13,161,010 width=32) (actual time=0.036..19,529.123 rows=13,161,010 loops=1)

4. 17,368.179 17,368.179 ↑ 1.0 13,161,010 1

Index Scan using pk_faersdemographic on faersdemographic x0_1 (cost=0.56..2,005,045.08 rows=13,161,010 width=32) (actual time=0.035..17,368.179 rows=13,161,010 loops=1)

  • Filter: ((updoperation < 2) AND (updoperation < 2))
5.          

CTE cte_dstnctbase_3848145b2c1c40

6. 4,924.664 33,782.759 ↑ 1.0 13,161,010 1

WindowAgg (cost=2,449,270.29..2,712,490.49 rows=13,161,010 width=40) (actual time=27,823.461..33,782.759 rows=13,161,010 loops=1)

7. 6,955.458 28,858.095 ↑ 1.0 13,161,010 1

Sort (cost=2,449,270.29..2,482,172.82 rows=13,161,010 width=32) (actual time=27,823.453..28,858.095 rows=13,161,010 loops=1)

  • Sort Key: cte_base_3848145b2bf530."1", cte_base_3848145b2bf530."2
  • Sort Method: external sort  Disk: 540864kB
8. 21,902.637 21,902.637 ↑ 1.0 13,161,010 1

CTE Scan on cte_base_3848145b2bf530 (cost=0.00..263,220.20 rows=13,161,010 width=32) (actual time=0.036..21,902.637 rows=13,161,010 loops=1)

9.          

CTE cte_dstnct_3848145b2c1c40

10. 0.003 39,385.384 ↑ 1.0 2 1

Limit (cost=296,780.77..296,780.78 rows=2 width=32) (actual time=39,385.382..39,385.384 rows=2 loops=1)

11. 1,369.128 39,385.381 ↑ 32,902.5 2 1

Sort (cost=296,780.77..296,945.29 rows=65,805 width=32) (actual time=39,385.381..39,385.381 rows=2 loops=1)

  • Sort Key: cte_dstnctbase_3848145b2c1c40."2
  • Sort Method: top-N heapsort  Memory: 25kB
12. 38,016.253 38,016.253 ↓ 200.0 13,161,010 1

CTE Scan on cte_dstnctbase_3848145b2c1c40 (cost=0.00..296,122.72 rows=65,805 width=32) (actual time=27,823.463..38,016.253 rows=13,161,010 loops=1)

  • Filter: (rownumber = 1)
13. 0.121 66,275.863 ↑ 3.0 10 1

Sort (cost=870,653.93..870,654.01 rows=30 width=726) (actual time=66,275.862..66,275.863 rows=10 loops=1)

  • Sort Key: x0.keyfaersdemographic, t1_0.keyaioidconfidencemapping, x0.mpfdaage, x3.mpfdaagecode, x4.mpfdaagegroupcode, x0.mpfdaauthnum, x0.mpfdacaseid, x0.mpfdacaseversion, x0.mpfdaconfid, x0.mpfdadeathdt, x0.mpfdaesub, x0.mpfdaeventdt, x0.mpfdafdadt, x0.mpfdafollseq, x0.mpfdaimage, x0.mpfdainitdt, x0.mpfdalitref, x0.mpfdamfrdt, x0.mpfdamfrnum, x0.mpfdamfrsndr, x0.mpfdaoccrcountry, x7.mpfdaoccupationcode, x5.mpfdapatientgendercode, x0.mpfdapatientweight, x6.mpfdapatientweightcode, x0.mpfdareportercountry, x1.mpfdareportstatuscode, x2.mpfdareporttypecode, x0.mpfdareptdt, x0.mpfdatomfr, x0.keympfdaprimaryid, t1_0.aioidmappingasof, t1_0.batchsequence, t1_0.confidencerank, t1_0.keyaimodelversion, t1_0.keyminamespace, t1_0.keytable, t1_0.matchconfidence, t1_0.oid, t1_0.oidvendor
  • Sort Method: quicksort  Memory: 27kB
14. 0.020 66,275.742 ↑ 3.0 10 1

Nested Loop Left Join (cost=66.55..870,653.19 rows=30 width=726) (actual time=43,024.602..66,275.742 rows=10 loops=1)

15. 1,279.902 66,275.672 ↑ 1.0 2 1

Hash Semi Join (cost=65.99..870,650.99 rows=2 width=650) (actual time=43,024.566..66,275.672 rows=2 loops=1)

  • Hash Cond: (x0.keyfaersdemographic = cte_dstnct_3848145b2c1c40."1")
16. 3,276.752 25,610.376 ↑ 1.0 13,161,010 1

Hash Left Join (cost=65.93..836,103.25 rows=13,161,010 width=650) (actual time=0.175..25,610.376 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersoccupationcode = x7.keyfaersoccupationcode)
17. 2,899.075 22,333.612 ↑ 1.0 13,161,010 1

Hash Left Join (cost=53.59..800,561.81 rows=13,161,010 width=630) (actual time=0.153..22,333.612 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaerspatientweightcode = x6.keyfaerspatientweightcode)
18. 3,113.966 19,434.524 ↑ 1.0 13,161,010 1

Hash Left Join (cost=52.10..764,811.39 rows=13,161,010 width=576) (actual time=0.130..19,434.524 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaerspatientgender = x5.keyfaerspatientgender)
19. 2,933.900 16,320.548 ↑ 1.0 13,161,010 1

Hash Left Join (cost=50.75..723,942.08 rows=13,161,010 width=556) (actual time=0.108..16,320.548 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersagegroup = x4.keyfaersagegroup)
20. 3,055.543 13,386.638 ↑ 1.0 13,161,010 1

Hash Left Join (cost=34.91..689,331.07 rows=13,161,010 width=512) (actual time=0.084..13,386.638 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersagecode = x3.keyfaersagecode)
21. 3,153.325 10,331.080 ↑ 1.0 13,161,010 1

Hash Left Join (cost=33.33..652,067.00 rows=13,161,010 width=484) (actual time=0.060..10,331.080 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersreporttype = x2.keyfaersreporttype)
22. 3,170.160 7,177.745 ↑ 1.0 13,161,010 1

Hash Left Join (cost=17.16..617,107.47 rows=13,161,010 width=452) (actual time=0.039..7,177.745 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersreportstatus = x1.keyfaersreportstatus)
23. 4,007.571 4,007.571 ↑ 1.0 13,161,010 1

Seq Scan on faersdemographic x0 (cost=0.00..582,202.15 rows=13,161,010 width=448) (actual time=0.008..4,007.571 rows=13,161,010 loops=1)

  • Filter: ((updoperation < 2) AND (updoperation < 2))
24. 0.006 0.014 ↑ 11.0 13 1

Hash (cost=15.38..15.38 rows=143 width=12) (actual time=0.014..0.014 rows=13 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
25. 0.008 0.008 ↑ 11.0 13 1

Seq Scan on faersreportstatus x1 (cost=0.00..15.38 rows=143 width=12) (actual time=0.006..0.008 rows=13 loops=1)

  • Filter: (updoperation < 2)
26. 0.005 0.010 ↑ 6.8 18 1

Hash (cost=14.62..14.62 rows=123 width=40) (actual time=0.010..0.010 rows=18 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
27. 0.005 0.005 ↑ 6.8 18 1

Seq Scan on faersreporttype x2 (cost=0.00..14.62 rows=123 width=40) (actual time=0.003..0.005 rows=18 loops=1)

  • Filter: (updoperation < 2)
28. 0.007 0.015 ↓ 2.9 35 1

Hash (cost=1.44..1.44 rows=12 width=36) (actual time=0.015..0.015 rows=35 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 10kB
29. 0.008 0.008 ↓ 2.9 35 1

Seq Scan on faersagecode x3 (cost=0.00..1.44 rows=12 width=36) (actual time=0.003..0.008 rows=35 loops=1)

  • Filter: (updoperation < 2)
30. 0.005 0.010 ↑ 6.5 18 1

Hash (cost=14.38..14.38 rows=117 width=52) (actual time=0.010..0.010 rows=18 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
31. 0.005 0.005 ↑ 6.5 18 1

Seq Scan on faersagegroup x4 (cost=0.00..14.38 rows=117 width=52) (actual time=0.003..0.005 rows=18 loops=1)

  • Filter: (updoperation < 2)
32. 0.005 0.010 ↓ 3.0 21 1

Hash (cost=1.26..1.26 rows=7 width=28) (actual time=0.010..0.010 rows=21 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
33. 0.005 0.005 ↓ 3.0 21 1

Seq Scan on faerspatientgender x5 (cost=0.00..1.26 rows=7 width=28) (actual time=0.002..0.005 rows=21 loops=1)

  • Filter: (updoperation < 2)
34. 0.007 0.013 ↓ 2.9 29 1

Hash (cost=1.36..1.36 rows=10 width=62) (actual time=0.013..0.013 rows=29 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 10kB
35. 0.006 0.006 ↓ 2.9 29 1

Seq Scan on faerspatientweightcode x6 (cost=0.00..1.36 rows=10 width=62) (actual time=0.003..0.006 rows=29 loops=1)

  • Filter: (updoperation < 2)
36. 0.005 0.012 ↑ 2.2 21 1

Hash (cost=11.75..11.75 rows=47 width=28) (actual time=0.012..0.012 rows=21 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
37. 0.007 0.007 ↑ 2.2 21 1

Seq Scan on faersoccupationcode x7 (cost=0.00..11.75 rows=47 width=28) (actual time=0.005..0.007 rows=21 loops=1)

  • Filter: (updoperation < 2)
38. 0.008 39,385.394 ↑ 1.0 2 1

Hash (cost=0.04..0.04 rows=2 width=16) (actual time=39,385.394..39,385.394 rows=2 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
39. 39,385.386 39,385.386 ↑ 1.0 2 1

CTE Scan on cte_dstnct_3848145b2c1c40 (cost=0.00..0.04 rows=2 width=16) (actual time=39,385.383..39,385.386 rows=2 loops=1)

40. 0.050 0.050 ↑ 3.0 5 2

Index Scan using ai_aioidconfidencemapping_26071 on aioidconfidencemapping t1_0 (cost=0.56..0.95 rows=15 width=60) (actual time=0.023..0.025 rows=5 loops=2)

  • Index Cond: ((keytable = 117) AND (keyminamespace = 67) AND ((x0.mpfdamfrsndr)::text = (oidvendor)::text))