explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tG6o

Settings
# exclusive inclusive rows x rows loops node
1. 0.247 50.594 ↓ 463.0 463 1

Unique (cost=31,189.14..31,189.23 rows=1 width=320) (actual time=50.327..50.594 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.057 47.238 ↓ 1.2 352 1

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

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

Sort (cost=31,130.66..31,131.42 rows=302 width=32) (actual time=47.156..47.181 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.317 47.098 ↓ 1.2 352 1

Gather (cost=1,000.00..31,118.22 rows=302 width=32) (actual time=0.233..47.098 rows=352 loops=1)

  • Output: t0_0_1.keyfda510k, t0_0_1.keyfda510k
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=27726
6. 42.781 42.781 ↑ 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=16.795..42.781 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=40.648..40.648 rows=0 loops=1
  • Buffers: shared hit=9224
  • Worker 1: actual time=9.733..41.564 rows=83 loops=1
  • Buffers: shared hit=9224
7.          

CTE cte_dstnctbase_384b2986a32e94

8. 0.151 47.509 ↓ 1.2 352 1

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

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

Sort (cost=18.48..19.23 rows=302 width=32) (actual time=47.341..47.358 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.300 47.300 ↓ 1.2 352 1

CTE Scan on cte_base_384b2986a32e94 (cost=0.00..6.04 rows=302 width=32) (actual time=47.161..47.300 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 47.679 ↓ 50.5 101 1

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

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

Sort (cost=6.80..6.81 rows=2 width=32) (actual time=47.664..47.669 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.604 47.604 ↓ 176.0 352 1

CTE Scan on cte_dstnctbase_384b2986a32e94 (cost=0.00..6.79 rows=2 width=32) (actual time=47.347..47.604 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.746 50.347 ↓ 463.0 463 1

Sort (cost=25.64..25.65 rows=1 width=320) (actual time=50.326..50.347 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.169 49.601 ↓ 463.0 463 1

Nested Loop Left Join (cost=1.03..25.63 rows=1 width=320) (actual time=47.759..49.601 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.070 48.220 ↓ 101.0 101 1

Nested Loop (cost=0.47..17.05 rows=1 width=244) (actual time=47.733..48.220 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.025 47.746 ↓ 50.5 101 1

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

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

Sort (cost=0.05..0.06 rows=2 width=16) (actual time=47.712..47.721 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.697 47.697 ↓ 50.5 101 1

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

  • Output: cte_dstnct_384b2986a32e94."1
  • Buffers: shared hit=27726
21. 0.404 0.404 ↑ 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.004..0.004 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.212 1.212 ↓ 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.010..0.012 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