explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UGZi

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 1.039 ↓ 16.0 16 1

GroupAggregate (cost=10.17..10.23 rows=1 width=270) (actual time=1.022..1.039 rows=16 loops=1)

  • Output: (((ei.extrainfoid)::text || '_image'::text)), (((a.assetid)::text || '_image'::text)), (((ei.extrainfoid)::text || '_ei'::text)), a.assetid, a.customerid, max(co.contractid), l.linkid, lt.name, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid
  • Group Key: (((ei.extrainfoid)::text || '_image'::text)), (((a.assetid)::text || '_image'::text)), (((ei.extrainfoid)::text || '_ei'::text)), a.assetid, l.linkid, lt.name, ei.extrainfoid
  • Buffers: shared hit=394 read=20 dirtied=2
2. 0.064 1.017 ↓ 16.0 16 1

Sort (cost=10.17..10.18 rows=1 width=270) (actual time=1.015..1.017 rows=16 loops=1)

  • Output: (((ei.extrainfoid)::text || '_image'::text)), (((a.assetid)::text || '_image'::text)), (((ei.extrainfoid)::text || '_ei'::text)), a.assetid, l.linkid, lt.name, ei.extrainfoid, a.customerid, co.contractid, ei.stringvalue, ei.assetmatchingid
  • Sort Key: (((ei.extrainfoid)::text || '_image'::text)), (((a.assetid)::text || '_image'::text)), (((ei.extrainfoid)::text || '_ei'::text)), a.assetid, l.linkid, lt.name, ei.extrainfoid
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=394 read=20 dirtied=2
3. 0.016 0.953 ↓ 16.0 16 1

Nested Loop Anti Join (cost=2.47..10.16 rows=1 width=270) (actual time=0.309..0.953 rows=16 loops=1)

  • Output: ((ei.extrainfoid)::text || '_image'::text), ((a.assetid)::text || '_image'::text), ((ei.extrainfoid)::text || '_ei'::text), a.assetid, l.linkid, lt.name, ei.extrainfoid, a.customerid, co.contractid, ei.stringvalue, ei.assetmatchingid
  • Buffers: shared hit=388 read=20 dirtied=2
4. 0.021 0.861 ↓ 19.0 19 1

Nested Loop Left Join (cost=2.34..9.52 rows=1 width=174) (actual time=0.290..0.861 rows=19 loops=1)

  • Output: a.assetid, a.customerid, l.linkid, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid, lt.name, co.contractid
  • Inner Unique: true
  • Join Filter: (ems.assetmlmodelid = mlmodel.assetmlmodelid)
  • Rows Removed by Join Filter: 12
  • Filter: (ems.scoring IS NULL)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=368 read=18 dirtied=2
5. 0.030 0.790 ↓ 25.0 25 1

Nested Loop (cost=2.20..9.13 rows=1 width=178) (actual time=0.253..0.790 rows=25 loops=1)

  • Output: a.assetid, a.customerid, l.linkid, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid, lt.name, co.contractid, mlmodel.assetmlmodelid
  • Inner Unique: true
  • Buffers: shared hit=330 read=16 dirtied=2
6. 0.022 0.735 ↓ 25.0 25 1

Nested Loop (cost=2.06..8.75 rows=1 width=178) (actual time=0.244..0.735 rows=25 loops=1)

  • Output: a.assetid, a.customerid, l.linkid, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid, lt.name, coa.contractid, mlmodel.assetmlmodelid
  • Inner Unique: true
  • Buffers: shared hit=280 read=16 dirtied=2
7. 0.025 0.663 ↓ 25.0 25 1

Nested Loop (cost=1.91..8.44 rows=1 width=64) (actual time=0.213..0.663 rows=25 loops=1)

  • Output: a.assetid, a.customerid, l.linkid, l.linktypeid, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid, coa.contractid, mlmodel.assetmlmodelid
  • Buffers: shared hit=232 read=14 dirtied=2
8. 0.049 0.354 ↓ 71.0 142 1

Nested Loop (cost=1.77..7.90 rows=2 width=28) (actual time=0.145..0.354 rows=142 loops=1)

  • Output: a.assetid, a.customerid, l.linkid, l.customerid, l.linktypeid, coa.contractid, mlmodel.assetmlmodelid
  • Buffers: shared hit=76 read=10
9. 0.007 0.137 ↓ 2.0 6 1

Nested Loop (cost=1.49..5.43 rows=3 width=24) (actual time=0.099..0.137 rows=6 loops=1)

  • Output: a.assetid, a.customerid, coa.assetid, coa.contractid, mlmodel.assetid, mlmodel.assetmlmodelid
  • Inner Unique: true
  • Buffers: shared hit=21 read=2
10. 0.031 0.094 ↓ 1.5 6 1

Hash Join (cost=1.35..3.41 rows=4 width=16) (actual time=0.075..0.094 rows=6 loops=1)

  • Output: coa.assetid, coa.contractid, mlmodel.assetid, mlmodel.assetmlmodelid
  • Hash Cond: (coa.assetid = mlmodel.assetid)
  • Buffers: shared hit=1 read=1
11. 0.015 0.015 ↑ 1.0 58 1

Seq Scan on rpcore.contract_asset coa (cost=0.00..1.58 rows=58 width=8) (actual time=0.008..0.015 rows=58 loops=1)

  • Output: coa.contractassetid, coa.contractid, coa.assetid, coa.createdby, coa.createdat, coa.modifiedby, coa.modifiedat
  • Buffers: shared hit=1
12. 0.006 0.048 ↓ 1.5 6 1

Hash (cost=1.30..1.30 rows=4 width=8) (actual time=0.048..0.048 rows=6 loops=1)

  • Output: mlmodel.assetid, mlmodel.assetmlmodelid
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
13. 0.042 0.042 ↓ 1.5 6 1

Seq Scan on rpcore.assetmlmodel mlmodel (cost=0.00..1.30 rows=4 width=8) (actual time=0.038..0.042 rows=6 loops=1)

  • Output: mlmodel.assetid, mlmodel.assetmlmodelid
  • Filter: ((NOT mlmodel.deleted) AND (mlmodel.modeltypeid = 1))
  • Rows Removed by Filter: 18
  • Buffers: shared read=1
14. 0.036 0.036 ↑ 1.0 1 6

Index Scan using pk_asset on rpcore.asset a (cost=0.14..0.50 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=6)

  • Output: a.assetid, a.customerid
  • Index Cond: (a.assetid = coa.assetid)
  • Filter: a.active
  • Buffers: shared hit=16 read=1
15. 0.168 0.168 ↓ 24.0 24 6

Index Only Scan using link__cus_ass_link_lt_nodeleted on rpcore.link l (cost=0.27..0.81 rows=1 width=16) (actual time=0.012..0.028 rows=24 loops=6)

  • Output: l.customerid, l.assetid, l.linkid, l.linktypeid
  • Index Cond: ((l.customerid = a.customerid) AND (l.assetid = a.assetid))
  • Heap Fetches: 100
  • Buffers: shared hit=55 read=8
16. 0.284 0.284 ↓ 0.0 0 142

Index Scan using idx_extrainfo_customer_link_extrainfotype_assetmatching on rpcore.extrainfo ei (cost=0.15..0.26 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=142)

  • Output: ei.extrainfoid, ei.stringvalue, ei.assetmatchingid, ei.linkid, ei.customerid
  • Index Cond: ((ei.customerid = l.customerid) AND (ei.linkid = l.linkid) AND (ei.extrainfotypeid = 1) AND (ei.assetmatchingid = 1))
  • Buffers: shared hit=156 read=4 dirtied=2
17. 0.050 0.050 ↑ 1.0 1 25

Index Scan using pk_linktype on rpcore.linktype lt (cost=0.15..0.31 rows=1 width=122) (actual time=0.002..0.002 rows=1 loops=25)

  • Output: lt.linktypeid, lt.name, lt.literalkey
  • Index Cond: (lt.linktypeid = l.linktypeid)
  • Buffers: shared hit=48 read=2
18. 0.025 0.025 ↑ 1.0 1 25

Index Scan using pk_contract on rpcore.contract co (cost=0.14..0.36 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=25)

  • Output: co.contractid
  • Index Cond: (co.contractid = coa.contractid)
  • Filter: co.active
  • Buffers: shared hit=50
19. 0.050 0.050 ↑ 1.0 1 25

Index Scan using idx_exinfmlmodelscoring_extrainfoid on rpcore.extrainfo_mlmodel_scoring ems (cost=0.14..0.38 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=25)

  • Output: ems.extrainfomlmodelscoringid, ems.extrainfoid, ems.assetmlmodelid, ems.scoring, ems.closestassetdocumentid, ems.matches, ems.createdby, ems.createdat, ems.modifiedby, ems.modifiedat
  • Index Cond: (ems.extrainfoid = ei.extrainfoid)
  • Buffers: shared hit=38 read=2
20. 0.076 0.076 ↓ 0.0 0 19

Index Only Scan using idx_recognitionimagepredictionrequest_extrainfo on rpcore.recognitionimagepredictionrequest req (cost=0.13..0.37 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=19)

  • Output: req.extrainfoid
  • Index Cond: (req.extrainfoid = ei.extrainfoid)
  • Heap Fetches: 3
  • Buffers: shared hit=20 read=2
Planning time : 25.454 ms
Execution time : 1.342 ms