explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6ABN

Settings
# exclusive inclusive rows x rows loops node
1. 0.245 50.232 ↓ 463.0 463 1

Unique (cost=31,189.14..31,189.23 rows=1 width=320) (actual time=49.967..50.232 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=28642
2.          

CTE cte_base_384b2986a32e94

3. 0.058 47.274 ↓ 1.2 352 1

Unique (cost=31,130.66..31,132.17 rows=302 width=32) (actual time=47.192..47.274 rows=352 loops=1)

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

Sort (cost=31,130.66..31,131.42 rows=302 width=32) (actual time=47.192..47.216 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.113 47.129 ↓ 1.2 352 1

Gather (cost=1,000.00..31,118.22 rows=302 width=32) (actual time=0.220..47.129 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.016 43.016 ↑ 1.1 117 3 / 3

Parallel Seq Scan on mpdata.fda510k t0_0_1 (cost=0.00..30,088.02 rows=126 width=32) (actual time=7.056..43.016 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.687..41.387 rows=82 loops=1
  • Buffers: shared hit=8712
  • Worker 1: actual time=9.477..41.770 rows=1 loops=1
  • Buffers: shared hit=9736
7.          

CTE cte_dstnctbase_384b2986a32e94

8. 0.138 47.540 ↓ 1.2 352 1

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

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

Sort (cost=18.48..19.23 rows=302 width=32) (actual time=47.386..47.402 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. 47.333 47.333 ↓ 1.2 352 1

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

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

CTE cte_dstnct_384b2986a32e94

12. 0.009 47.712 ↓ 50.5 101 1

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

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

Sort (cost=6.80..6.81 rows=2 width=32) (actual time=47.698..47.703 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. 47.635 47.635 ↓ 176.0 352 1

CTE Scan on cte_dstnctbase_384b2986a32e94 (cost=0.00..6.79 rows=2 width=32) (actual time=47.391..47.635 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.665 49.987 ↓ 463.0 463 1

Sort (cost=25.64..25.65 rows=1 width=320) (actual time=49.966..49.987 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=28642
16. 0.146 49.322 ↓ 463.0 463 1

Nested Loop Left Join (cost=1.03..25.63 rows=1 width=320) (actual time=47.783..49.322 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=28642
17. 0.096 48.166 ↓ 101.0 101 1

Nested Loop (cost=0.47..17.05 rows=1 width=244) (actual time=47.759..48.166 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=28132
18. 0.017 47.767 ↓ 50.5 101 1

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

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

Sort (cost=0.05..0.06 rows=2 width=16) (actual time=47.744..47.750 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. 47.728 47.728 ↓ 50.5 101 1

CTE Scan on cte_dstnct_384b2986a32e94 (cost=0.00..0.04 rows=2 width=16) (actual time=47.700..47.728 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 ix_fda510k_sk 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=406
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