explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cENX

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 71,259.356 ↑ 3.0 10 1

Unique (cost=5,917,872.81..5,917,875.89 rows=30 width=726) (actual time=71,259.346..71,259.356 rows=10 loops=1)

  • Buffers: shared hit=440163, temp read=67608 written=215411
2.          

CTE cte_base_3848145b2bf530

3. 2,525.603 21,076.305 ↑ 1.0 13,161,010 1

Unique (cost=0.56..2,037,947.61 rows=13,161,010 width=32) (actual time=0.035..21,076.305 rows=13,161,010 loops=1)

  • Buffers: shared hit=55359
4. 18,550.702 18,550.702 ↑ 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.033..18,550.702 rows=13,161,010 loops=1)

  • Filter: ((x0_1.updoperation < 2) AND (x0_1.updoperation < 2))
  • Buffers: shared hit=55359
5.          

CTE cte_dstnctbase_3848145b2c1c40

6. 4,977.617 36,465.620 ↑ 1.0 13,161,010 1

WindowAgg (cost=2,449,270.29..2,712,490.49 rows=13,161,010 width=40) (actual time=30,392.027..36,465.62 rows=13,161,010 loops=1)

  • Buffers: shared hit=55359, temp read=67608 written=135083
7. 7,842.738 31,488.003 ↑ 1.0 13,161,010 1

Sort (cost=2,449,270.29..2,482,172.82 rows=13,161,010 width=32) (actual time=30,392.018..31,488.003 rows=13,161,010 loops=1)

  • Sort Key: cte_base_3848145b2bf530."1", cte_base_3848145b2bf530."2"
  • Sort Method: external sort Disk: 540864kB
  • Buffers: shared hit=55359, temp read=67608 written=135083
8. 23,645.265 23,645.265 ↑ 1.0 13,161,010 1

CTE Scan on cte_base_3848145b2bf530 cte_base_3848145b2bf530 (cost=0..263,220.2 rows=13,161,010 width=32) (actual time=0.036..23,645.265 rows=13,161,010 loops=1)

  • Buffers: shared hit=55359, temp written=67475
9.          

CTE cte_dstnct_3848145b2c1c40

10. 0.002 42,345.727 ↑ 1.0 2 1

Limit (cost=296,780.77..296,780.78 rows=2 width=32) (actual time=42,345.725..42,345.727 rows=2 loops=1)

  • Buffers: shared hit=55359, temp read=67608 written=215411
11. 1,459.648 42,345.725 ↑ 32,902.5 2 1

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

  • Sort Key: cte_dstnctbase_3848145b2c1c40."2"
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=55359, temp read=67608 written=215411
12. 40,886.077 40,886.077 ↓ 200.0 13,161,010 1

CTE Scan on cte_dstnctbase_3848145b2c1c40 cte_dstnctbase_3848145b2c1c40 (cost=0..296,122.72 rows=65,805 width=32) (actual time=30,392.029..40,886.077 rows=13,161,010 loops=1)

  • Filter: (cte_dstnctbase_3848145b2c1c40.rownumber = 1)
  • Buffers: shared hit=55359, temp read=67608 written=215411
13. 0.060 71,259.342 ↑ 3.0 10 1

Sort (cost=870,653.93..870,654.01 rows=30 width=726) (actual time=71,259.341..71,259.342 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
  • Buffers: shared hit=440163, temp read=67608 written=215411
14. 0.030 71,259.282 ↑ 3.0 10 1

Nested Loop (cost=66.55..870,653.19 rows=30 width=726) (actual time=46,250.524..71,259.282 rows=10 loops=1)

  • Buffers: shared hit=440163, temp read=67608 written=215411
15. 1,516.496 71,259.194 ↑ 1.0 2 1

Hash Join (cost=65.99..870,650.99 rows=2 width=650) (actual time=46,250.475..71,259.194 rows=2 loops=1)

  • Buffers: shared hit=440153, temp read=67608 written=215411
16. 3,443.737 27,396.962 ↑ 1.0 13,161,010 1

Hash Join (cost=65.93..836,103.25 rows=13,161,010 width=650) (actual time=0.159..27,396.962 rows=13,161,010 loops=1)

  • Buffers: shared hit=384794
17. 3,140.414 23,953.213 ↑ 1.0 13,161,010 1

Hash Join (cost=53.59..800,561.81 rows=13,161,010 width=630) (actual time=0.138..23,953.213 rows=13,161,010 loops=1)

  • Buffers: shared hit=384793
18. 3,351.758 20,812.786 ↑ 1.0 13,161,010 1

Hash Join (cost=52.1..764,811.39 rows=13,161,010 width=576) (actual time=0.12..20,812.786 rows=13,161,010 loops=1)

  • Buffers: shared hit=384792
19. 3,193.825 17,461.017 ↑ 1.0 13,161,010 1

Hash Join (cost=50.75..723,942.08 rows=13,161,010 width=556) (actual time=0.099..17,461.017 rows=13,161,010 loops=1)

  • Buffers: shared hit=384791
20. 3,293.461 14,267.182 ↑ 1.0 13,161,010 1

Hash Join (cost=34.91..689,331.07 rows=13,161,010 width=512) (actual time=0.076..14,267.182 rows=13,161,010 loops=1)

  • Buffers: shared hit=384790
21. 3,469.291 10,973.705 ↑ 1.0 13,161,010 1

Hash Join (cost=33.33..652,067 rows=13,161,010 width=484) (actual time=0.05..10,973.705 rows=13,161,010 loops=1)

  • Buffers: shared hit=384789
22. 3,353.960 7,504.405 ↑ 1.0 13,161,010 1

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

  • Buffers: shared hit=384788
23. 4,150.436 4,150.436 ↑ 1.0 13,161,010 1

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

  • Filter: ((x0.updoperation < 2) AND (x0.updoperation < 2))
  • Buffers: shared hit=384787
24. 0.003 0.009 ↑ 11.0 13 1

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

  • Buffers: shared hit=1
25. 0.006 0.006 ↑ 11.0 13 1

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

  • Filter: (x1.updoperation < 2)
  • Buffers: shared hit=1
26. 0.004 0.009 ↑ 6.8 18 1

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

  • Buffers: shared hit=1
27. 0.005 0.005 ↑ 6.8 18 1

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

  • Filter: (x2.updoperation < 2)
  • Buffers: shared hit=1
28. 0.006 0.016 ↓ 2.9 35 1

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

  • Buffers: shared hit=1
29. 0.010 0.010 ↓ 2.9 35 1

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

  • Filter: (x3.updoperation < 2)
  • Buffers: shared hit=1
30. 0.005 0.010 ↑ 6.5 18 1

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

  • Buffers: shared hit=1
31. 0.005 0.005 ↑ 6.5 18 1

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

  • Filter: (x4.updoperation < 2)
  • Buffers: shared hit=1
32. 0.006 0.011 ↓ 3.0 21 1

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

  • Buffers: shared hit=1
33. 0.005 0.005 ↓ 3.0 21 1

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

  • Filter: (x5.updoperation < 2)
  • Buffers: shared hit=1
34. 0.007 0.013 ↓ 2.9 29 1

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

  • Buffers: shared hit=1
35. 0.006 0.006 ↓ 2.9 29 1

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

  • Filter: (x6.updoperation < 2)
  • Buffers: shared hit=1
36. 0.006 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)

  • Buffers: shared hit=1
37. 0.006 0.006 ↑ 2.2 21 1

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

  • Filter: (x7.updoperation < 2)
  • Buffers: shared hit=1
38. 0.006 42,345.736 ↑ 1.0 2 1

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

  • Buffers: shared hit=55359, temp read=67608 written=215411
39. 42,345.730 42,345.730 ↑ 1.0 2 1

CTE Scan on cte_dstnct_3848145b2c1c40 cte_dstnct_3848145b2c1c40 (cost=0..0.04 rows=2 width=16) (actual time=42,345.727..42,345.73 rows=2 loops=1)

  • Buffers: shared hit=55359, temp read=67608 written=215411
40. 0.058 0.058 ↑ 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.027..0.029 rows=5 loops=2)

  • Index Cond: ((t1_0.keytable = 117) AND (t1_0.keyminamespace = 67) AND ((x0.mpfdamfrsndr)::text = (t1_0.oidvendor)::text))
  • Buffers: shared hit=10
Planning time : 5.923 ms
Execution time : 71,493.387 ms