explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ANkg

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

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

CTE cte_base_384b2986a32e94

3. 0.057 48.693 ↓ 1.2 352 1

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

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

Sort (cost=31,130.66..31,131.42 rows=302 width=32) (actual time=48.611..48.636 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.625 48.554 ↓ 1.2 352 1

Gather (cost=1,000.00..31,118.22 rows=302 width=32) (actual time=0.243..48.554 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.929 43.929 ↑ 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=17.200..43.929 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=41.703..41.703 rows=0 loops=1
  • Buffers: shared hit=8510
  • Worker 1: actual time=9.891..42.400 rows=100 loops=1
  • Buffers: shared hit=9480
7.          

CTE cte_dstnctbase_384b2986a32e94

8. 0.139 48.953 ↓ 1.2 352 1

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

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

Sort (cost=18.48..19.23 rows=302 width=32) (actual time=48.797..48.814 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.748 48.748 ↓ 1.2 352 1

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

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

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

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

CTE Scan on cte_dstnctbase_384b2986a32e94 (cost=0.00..6.79 rows=2 width=32) (actual time=48.803..49.048 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.679 51.490 ↓ 463.0 463 1

Sort (cost=25.64..25.65 rows=1 width=320) (actual time=51.469..51.490 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=28644
16. 0.192 50.811 ↓ 463.0 463 1

Nested Loop Left Join (cost=1.03..25.63 rows=1 width=320) (actual time=49.197..50.811 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=28644
17. 0.025 49.609 ↓ 101.0 101 1

Nested Loop (cost=0.47..17.05 rows=1 width=244) (actual time=49.174..49.609 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 49.180 ↓ 50.5 101 1

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

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

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

CTE Scan on cte_dstnct_384b2986a32e94 (cost=0.00..0.04 rows=2 width=16) (actual time=49.113..49.141 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.010 1.010 ↓ 5.0 5 101

Index Scan using ix_aioidconfidencemapping_sk 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: (((t0_0.keympfdaknumber)::text = (t1_0.oidvendor)::text) AND (t1_0.keytable = 117) AND (t1_0.keyminamespace = 66))
  • Filter: (t1_0.updoperation < 2)
  • Buffers: shared hit=514