explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rTdp

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 27,008.651 ↑ 1.5 10 1

Unique (cost=870,580.01..870,581.54 rows=15 width=726) (actual time=27,008.636..27,008.651 rows=10 loops=1)

2.          

CTE cte_base_3848145b2bf530

3. 0.000 0.037 ↑ 1.0 2 1

Limit (cost=0.56..0.87 rows=2 width=32) (actual time=0.034..0.037 rows=2 loops=1)

4. 0.003 0.037 ↑ 6,579,293.5 2 1

Unique (cost=0.56..2,037,842.86 rows=13,158,587 width=32) (actual time=0.034..0.037 rows=2 loops=1)

5. 0.034 0.034 ↑ 6,579,293.5 2 1

Index Scan using pk_faersdemographic on faersdemographic x0_1 (cost=0.56..2,004,946.39 rows=13,158,587 width=32) (actual time=0.032..0.034 rows=2 loops=1)

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

CTE cte_dstnctbase_3848145b2c1c40

7. 0.006 0.050 ↑ 1.0 2 1

WindowAgg (cost=0.05..0.09 rows=2 width=40) (actual time=0.048..0.050 rows=2 loops=1)

8. 0.006 0.044 ↑ 1.0 2 1

Sort (cost=0.05..0.06 rows=2 width=32) (actual time=0.044..0.044 rows=2 loops=1)

  • Sort Key: cte_base_3848145b2bf530."1", cte_base_3848145b2bf530."2
  • Sort Method: quicksort Memory: 25kB
9. 0.038 0.038 ↑ 1.0 2 1

CTE Scan on cte_base_3848145b2bf530 (cost=0.00..0.04 rows=2 width=32) (actual time=0.035..0.038 rows=2 loops=1)

10.          

CTE cte_dstnct_3848145b2c1c40

11. 0.001 0.058 ↓ 2.0 2 1

Limit (cost=0.06..0.06 rows=1 width=32) (actual time=0.058..0.058 rows=2 loops=1)

12. 0.005 0.057 ↓ 2.0 2 1

Sort (cost=0.06..0.06 rows=1 width=32) (actual time=0.057..0.057 rows=2 loops=1)

  • Sort Key: cte_dstnctbase_3848145b2c1c40."2
  • Sort Method: quicksort Memory: 25kB
13. 0.052 0.052 ↓ 2.0 2 1

CTE Scan on cte_dstnctbase_3848145b2c1c40 (cost=0.00..0.04 rows=1 width=32) (actual time=0.049..0.052 rows=2 loops=1)

  • Filter: (rownumber = 1)
14. 0.056 27,008.635 ↑ 1.5 10 1

Sort (cost=870,578.99..870,579.02 rows=15 width=726) (actual time=27,008.634..27,008.635 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
15. 0.020 27,008.579 ↑ 1.5 10 1

Nested Loop Left Join (cost=66.52..870,578.69 rows=15 width=726) (actual time=3,637.370..27,008.579 rows=10 loops=1)

16. 1,373.167 27,008.499 ↓ 2.0 2 1

Hash Semi Join (cost=65.96..870,577.59 rows=1 width=650) (actual time=3,637.327..27,008.499 rows=2 loops=1)

  • Hash Cond: (x0.keyfaersdemographic = cte_dstnct_3848145b2c1c40."1")
17. 3,192.997 25,635.267 ↓ 1.0 13,161,010 1

Hash Left Join (cost=65.93..836,036.26 rows=13,158,587 width=650) (actual time=0.103..25,635.267 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersoccupationcode = x7.keyfaersoccupationcode)
18. 2,847.866 22,442.262 ↓ 1.0 13,161,010 1

Hash Left Join (cost=53.59..800,498.37 rows=13,158,587 width=630) (actual time=0.090..22,442.262 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaerspatientweightcode = x6.keyfaerspatientweightcode)
19. 3,151.376 19,594.385 ↓ 1.0 13,161,010 1

Hash Left Join (cost=52.10..764,748.97 rows=13,158,587 width=576) (actual time=0.074..19,594.385 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaerspatientgender = x5.keyfaerspatientgender)
20. 2,835.132 16,443.000 ↓ 1.0 13,161,010 1

Hash Left Join (cost=50.75..723,876.74 rows=13,158,587 width=556) (actual time=0.061..16,443.000 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersagegroup = x4.keyfaersagegroup)
21. 3,130.653 13,607.860 ↓ 1.0 13,161,010 1

Hash Left Join (cost=34.91..689,271.25 rows=13,158,587 width=512) (actual time=0.050..13,607.860 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersagecode = x3.keyfaersagecode)
22. 3,100.826 10,477.194 ↓ 1.0 13,161,010 1

Hash Left Join (cost=33.33..652,017.80 rows=13,158,587 width=484) (actual time=0.031..10,477.194 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersreporttype = x2.keyfaersreporttype)
23. 3,273.803 7,376.360 ↓ 1.0 13,161,010 1

Hash Left Join (cost=17.16..617,064.69 rows=13,158,587 width=452) (actual time=0.018..7,376.360 rows=13,161,010 loops=1)

  • Hash Cond: (x0.keyfaersreportstatus = x1.keyfaersreportstatus)
24. 4,102.549 4,102.549 ↓ 1.0 13,161,010 1

Seq Scan on faersdemographic x0 (cost=0.00..582,165.80 rows=13,158,587 width=448) (actual time=0.005..4,102.549 rows=13,161,010 loops=1)

  • Filter: ((updoperation < 2) AND (updoperation < 2))
25. 0.004 0.008 ↑ 11.0 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.004 0.004 ↑ 11.0 13 1

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

  • Filter: (updoperation < 2)
27. 0.004 0.008 ↑ 6.8 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.004 0.004 ↑ 6.8 18 1

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

  • Filter: (updoperation < 2)
29. 0.006 0.013 ↓ 2.9 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
30. 0.007 0.007 ↓ 2.9 35 1

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

  • Filter: (updoperation < 2)
31. 0.004 0.008 ↑ 6.5 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.004 0.004 ↑ 6.5 18 1

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

  • Filter: (updoperation < 2)
33. 0.004 0.009 ↓ 3.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 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.001..0.005 rows=21 loops=1)

  • Filter: (updoperation < 2)
35. 0.006 0.011 ↓ 2.9 29 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.005 0.005 ↓ 2.9 29 1

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

  • Filter: (updoperation < 2)
37. 0.004 0.008 ↑ 2.2 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.004 0.004 ↑ 2.2 21 1

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

  • Filter: (updoperation < 2)
39. 0.006 0.065 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.065..0.065 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.059 0.059 ↓ 2.0 2 1

CTE Scan on cte_dstnct_3848145b2c1c40 (cost=0.00..0.02 rows=1 width=16) (actual time=0.058..0.059 rows=2 loops=1)

41. 0.060 0.060 ↑ 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.028..0.030 rows=5 loops=2)

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