explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m5jn

Settings
# exclusive inclusive rows x rows loops node
1. 0.246 50.988 ↓ 463.0 463 1

Unique (cost=31,188.89..31,188.98 rows=1 width=320) (actual time=50.722..50.988 rows=463 loops=1)

  • Output: t0_0.keyfda510k, t0_0.mpfdaapplicant, t0_0.mpfdacity, t0_0.mpfdaclassadvisecommittee, t0_0.mpfdacontact, t0_0.mpfdacountrycode, t0_0.mpfdadatereceived, t0_0.mpfdadecision, t0_0.mpfdadecisiondate, t0_0.mpfdadevicename, t0_0.mpfdaexpeditedreview, t0_0.mpfdaforeignpostalcode, t0_0.mpfdaproductcode, t0_0.mpfdareviewadvisecommittee, t0_0.mpfdasspindicator, t0_0.mpfdastate, t0_0.mpfdastateorsumm, t0_0.mpfdastreet1, t0_0.mpfdastreet2, t0_0.mpfdasubmissiontype, t0_0.mpfdathirdparty, t0_0.mpfdazip, t0_0.keympfdaknumber, t1_0.keyaioidconfidencemapping, 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, t0_0.keyfda510k
  • Buffers: shared hit=28640
2.          

CTE cte_base_384b2986a32e94

3. 0.052 48.033 ↓ 1.2 352 1

Unique (cost=31,130.41..31,131.92 rows=302 width=32) (actual time=47.960..48.033 rows=352 loops=1)

  • Output: t0_0_1.keyfda510k, t0_0_1.keyfda510k
  • Buffers: shared hit=27726
4. 0.074 47.981 ↓ 1.2 352 1

Sort (cost=31,130.41..31,131.16 rows=302 width=32) (actual time=47.960..47.981 rows=352 loops=1)

  • Output: t0_0_1.keyfda510k, t0_0_1.keyfda510k
  • Sort Key: t0_0_1.keyfda510k
  • Sort Method: quicksort Memory: 52kB
  • Buffers: shared hit=27726
5. 4.223 47.907 ↓ 1.2 352 1

Gather (cost=1,000.00..31,117.97 rows=302 width=32) (actual time=0.237..47.907 rows=352 loops=1)

  • Output: t0_0_1.keyfda510k, t0_0_1.keyfda510k
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=27726
6. 43.684 43.684 ↑ 1.1 117 3 / 3

Parallel Seq Scan on mpdata.fda510k t0_0_1 (cost=0.00..30,087.77 rows=126 width=32) (actual time=7.169..43.684 rows=117 loops=3)

  • Output: t0_0_1.keyfda510k, t0_0_1.keyfda510k
  • Filter: (t0_0_1.updoperation < 2)
  • Rows Removed by Filter: 151052
  • Buffers: shared hit=27726
  • Worker 0: actual time=11.883..41.921 rows=99 loops=1
  • Buffers: shared hit=8712
  • Worker 1: actual time=9.621..42.043 rows=1 loops=1
  • Buffers: shared hit=9534
7.          

CTE cte_dstnctbase_384b2986a32e94

8. 0.150 48.298 ↓ 1.2 352 1

WindowAgg (cost=18.48..24.52 rows=302 width=40) (actual time=48.136..48.298 rows=352 loops=1)

  • Output: row_number() OVER (?), cte_base_384b2986a32e94."1", cte_base_384b2986a32e94."2
  • Buffers: shared hit=27726
9. 0.055 48.148 ↓ 1.2 352 1

Sort (cost=18.48..19.23 rows=302 width=32) (actual time=48.131..48.148 rows=352 loops=1)

  • Output: cte_base_384b2986a32e94."1", cte_base_384b2986a32e94."2
  • Sort Key: cte_base_384b2986a32e94."1", cte_base_384b2986a32e94."2
  • Sort Method: quicksort Memory: 52kB
  • Buffers: shared hit=27726
10. 48.093 48.093 ↓ 1.2 352 1

CTE Scan on cte_base_384b2986a32e94 (cost=0.00..6.04 rows=302 width=32) (actual time=47.961..48.093 rows=352 loops=1)

  • Output: cte_base_384b2986a32e94."1", cte_base_384b2986a32e94."2
  • Buffers: shared hit=27726
11.          

CTE cte_dstnct_384b2986a32e94

12. 0.010 48.470 ↓ 50.5 101 1

Limit (cost=6.80..6.81 rows=2 width=32) (actual time=48.457..48.470 rows=101 loops=1)

  • Output: cte_dstnctbase_384b2986a32e94."1", cte_dstnctbase_384b2986a32e94."2
  • Buffers: shared hit=27726
13. 0.066 48.460 ↓ 50.5 101 1

Sort (cost=6.80..6.81 rows=2 width=32) (actual time=48.455..48.460 rows=101 loops=1)

  • Output: cte_dstnctbase_384b2986a32e94."1", cte_dstnctbase_384b2986a32e94."2
  • Sort Key: cte_dstnctbase_384b2986a32e94."2
  • Sort Method: top-N heapsort Memory: 32kB
  • Buffers: shared hit=27726
14. 48.394 48.394 ↓ 176.0 352 1

CTE Scan on cte_dstnctbase_384b2986a32e94 (cost=0.00..6.79 rows=2 width=32) (actual time=48.137..48.394 rows=352 loops=1)

  • Output: cte_dstnctbase_384b2986a32e94."1", cte_dstnctbase_384b2986a32e94."2
  • Filter: (cte_dstnctbase_384b2986a32e94.rownumber = 1)
  • Buffers: shared hit=27726
15. 0.657 50.742 ↓ 463.0 463 1

Sort (cost=25.64..25.65 rows=1 width=320) (actual time=50.721..50.742 rows=463 loops=1)

  • Output: t0_0.keyfda510k, t0_0.mpfdaapplicant, t0_0.mpfdacity, t0_0.mpfdaclassadvisecommittee, t0_0.mpfdacontact, t0_0.mpfdacountrycode, t0_0.mpfdadatereceived, t0_0.mpfdadecision, t0_0.mpfdadecisiondate, t0_0.mpfdadevicename, t0_0.mpfdaexpeditedreview, t0_0.mpfdaforeignpostalcode, t0_0.mpfdaproductcode, t0_0.mpfdareviewadvisecommittee, t0_0.mpfdasspindicator, t0_0.mpfdastate, t0_0.mpfdastateorsumm, t0_0.mpfdastreet1, t0_0.mpfdastreet2, t0_0.mpfdasubmissiontype, t0_0.mpfdathirdparty, t0_0.mpfdazip, t0_0.keympfdaknumber, t1_0.keyaioidconfidencemapping, 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, t0_0.keyfda510k
  • Sort Key: t0_0.keyfda510k, t1_0.keyaioidconfidencemapping, t0_0.mpfdaapplicant, t0_0.mpfdacity, t0_0.mpfdaclassadvisecommittee, t0_0.mpfdacontact, t0_0.mpfdacountrycode, t0_0.mpfdadatereceived, t0_0.mpfdadecision, t0_0.mpfdadecisiondate, t0_0.mpfdadevicename, t0_0.mpfdaexpeditedreview, t0_0.mpfdaforeignpostalcode, t0_0.mpfdaproductcode, t0_0.mpfdareviewadvisecommittee, t0_0.mpfdasspindicator, t0_0.mpfdastate, t0_0.mpfdastateorsumm, t0_0.mpfdastreet1, t0_0.mpfdastreet2, t0_0.mpfdasubmissiontype, t0_0.mpfdathirdparty, t0_0.mpfdazip, t0_0.keympfdaknumber, 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: 265kB
  • Buffers: shared hit=28640
16. 0.160 50.085 ↓ 463.0 463 1

Nested Loop Left Join (cost=1.03..25.63 rows=1 width=320) (actual time=48.541..50.085 rows=463 loops=1)

  • Output: t0_0.keyfda510k, t0_0.mpfdaapplicant, t0_0.mpfdacity, t0_0.mpfdaclassadvisecommittee, t0_0.mpfdacontact, t0_0.mpfdacountrycode, t0_0.mpfdadatereceived, t0_0.mpfdadecision, t0_0.mpfdadecisiondate, t0_0.mpfdadevicename, t0_0.mpfdaexpeditedreview, t0_0.mpfdaforeignpostalcode, t0_0.mpfdaproductcode, t0_0.mpfdareviewadvisecommittee, t0_0.mpfdasspindicator, t0_0.mpfdastate, t0_0.mpfdastateorsumm, t0_0.mpfdastreet1, t0_0.mpfdastreet2, t0_0.mpfdasubmissiontype, t0_0.mpfdathirdparty, t0_0.mpfdazip, t0_0.keympfdaknumber, t1_0.keyaioidconfidencemapping, 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, t0_0.keyfda510k
  • Buffers: shared hit=28640
17. 0.085 48.915 ↓ 101.0 101 1

Nested Loop (cost=0.47..17.05 rows=1 width=244) (actual time=48.518..48.915 rows=101 loops=1)

  • Output: t0_0.keyfda510k, t0_0.mpfdaapplicant, t0_0.mpfdacity, t0_0.mpfdaclassadvisecommittee, t0_0.mpfdacontact, t0_0.mpfdacountrycode, t0_0.mpfdadatereceived, t0_0.mpfdadecision, t0_0.mpfdadecisiondate, t0_0.mpfdadevicename, t0_0.mpfdaexpeditedreview, t0_0.mpfdaforeignpostalcode, t0_0.mpfdaproductcode, t0_0.mpfdareviewadvisecommittee, t0_0.mpfdasspindicator, t0_0.mpfdastate, t0_0.mpfdastateorsumm, t0_0.mpfdastreet1, t0_0.mpfdastreet2, t0_0.mpfdasubmissiontype, t0_0.mpfdathirdparty, t0_0.mpfdazip, t0_0.keympfdaknumber
  • Inner Unique: true
  • Buffers: shared hit=28130
18. 0.018 48.527 ↓ 50.5 101 1

Unique (cost=0.05..0.06 rows=2 width=16) (actual time=48.502..48.527 rows=101 loops=1)

  • Output: cte_dstnct_384b2986a32e94."1
  • Buffers: shared hit=27726
19. 0.021 48.509 ↓ 50.5 101 1

Sort (cost=0.05..0.06 rows=2 width=16) (actual time=48.502..48.509 rows=101 loops=1)

  • Output: cte_dstnct_384b2986a32e94."1
  • Sort Key: cte_dstnct_384b2986a32e94."1
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=27726
20. 48.488 48.488 ↓ 50.5 101 1

CTE Scan on cte_dstnct_384b2986a32e94 (cost=0.00..0.04 rows=2 width=16) (actual time=48.457..48.488 rows=101 loops=1)

  • Output: cte_dstnct_384b2986a32e94."1
  • Buffers: shared hit=27726
21. 0.303 0.303 ↑ 1.0 1 101

Index Scan using pk_fda510k on mpdata.fda510k t0_0 (cost=0.42..8.44 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=101)

  • Output: t0_0.keyfda510k, t0_0.keympfdaknumber, t0_0.mpfdaapplicant, t0_0.mpfdacontact, t0_0.mpfdastreet1, t0_0.mpfdastreet2, t0_0.mpfdacity, t0_0.mpfdastate, t0_0.mpfdazip, t0_0.mpfdacountrycode, t0_0.mpfdadevicename, t0_0.mpfdadatereceived, t0_0.mpfdasubmissiontype, t0_0.mpfdadecisiondate, t0_0.mpfdadecision, t0_0.mpfdareviewadvisecommittee, t0_0.mpfdaproductcode, t0_0.mpfdaclassadvisecommittee, t0_0.mpfdastateorsumm, t0_0.mpfdasspindicator, t0_0.mpfdathirdparty, t0_0.mpfdaexpeditedreview, t0_0.mpfdaforeignpostalcode, t0_0.updoperation, t0_0.upddate
  • Index Cond: (t0_0.keyfda510k = cte_dstnct_384b2986a32e94."1")
  • Filter: (t0_0.updoperation < 2)
  • Buffers: shared hit=404
22. 1.010 1.010 ↓ 5.0 5 101

Index Scan using ai_aioidconfidencemapping_26530 on calcs2.aioidconfidencemapping t1_0 (cost=0.56..8.57 rows=1 width=60) (actual time=0.009..0.010 rows=5 loops=101)

  • Output: t1_0.keyaioidconfidencemapping, t1_0.keytable, t1_0.oid, t1_0.oidvendor, t1_0.keyminamespace, t1_0.keyaimodelversion, t1_0.matchconfidence, t1_0.confidencerank, t1_0.aioidmappingasof, t1_0.batchsequence, t1_0.updoperation, t1_0.upddate
  • Index Cond: ((t1_0.keytable = 117) AND (t1_0.keyminamespace = 66) AND ((t0_0.keympfdaknumber)::text = (t1_0.oidvendor)::text))
  • Filter: (t1_0.updoperation < 2)
  • Buffers: shared hit=510