explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ha9k

Settings
# exclusive inclusive rows x rows loops node
1. 0.072 140,813.574 ↓ 179.0 179 1

GroupAggregate (cost=848.20..848.26 rows=1 width=288) (actual time=140,813.505..140,813.574 rows=179 loops=1)

  • Output: (((ei.extrainfoid)::text || '_image'::text)), (((l.assetid)::text || '_image'::text)), (((ei.extrainfoid)::text || '_ei'::text)), l.assetid, l.customerid, max(co.contractid), l.linkid, lt.name, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid
  • Group Key: (((ei.extrainfoid)::text || '_image'::text)), (((l.assetid)::text || '_image'::text)), (((ei.extrainfoid)::text || '_ei'::text)), l.linkid, lt.name, ei.extrainfoid
  • Buffers: shared hit=4,997,944 read=403,375 dirtied=555
  • I/O Timings: read=134,413.330
2. 0.721 140,813.502 ↓ 179.0 179 1

Sort (cost=848.20..848.21 rows=1 width=288) (actual time=140,813.493..140,813.502 rows=179 loops=1)

  • Output: (((ei.extrainfoid)::text || '_image'::text)), (((l.assetid)::text || '_image'::text)), (((ei.extrainfoid)::text || '_ei'::text)), l.linkid, lt.name, ei.extrainfoid, l.assetid, l.customerid, co.contractid, ei.stringvalue, ei.assetmatchingid
  • Sort Key: (((ei.extrainfoid)::text || '_image'::text)), (((l.assetid)::text || '_image'::text)), (((ei.extrainfoid)::text || '_ei'::text)), l.linkid, lt.name, ei.extrainfoid
  • Sort Method: quicksort Memory: 80kB
  • Buffers: shared hit=4,997,944 read=403,375 dirtied=555
  • I/O Timings: read=134,413.330
3. 26.653 140,812.781 ↓ 179.0 179 1

Nested Loop Left Join (cost=3.40..848.19 rows=1 width=288) (actual time=7,232.435..140,812.781 rows=179 loops=1)

  • Output: ((ei.extrainfoid)::text || '_image'::text), ((l.assetid)::text || '_image'::text), ((ei.extrainfoid)::text || '_ei'::text), l.linkid, lt.name, ei.extrainfoid, l.assetid, l.customerid, co.contractid, ei.stringvalue, ei.assetmatchingid
  • Filter: ((COALESCE(req.quantity, 0) <= 5) AND ((req.extrainfoid IS NULL) OR ((req.lastrequest <= (statement_timestamp() - ('100HOUR'::cstring)::interval)) AND (req.imagerecognitionresultstatusid = 3) AND (req.executiontypeid <> 3))))
  • Rows Removed by Filter: 6,544
  • Buffers: shared hit=4,997,935 read=403,375 dirtied=555
  • I/O Timings: read=134,413.330
4. 14.535 138,883.519 ↓ 6,723.0 6,723 1

Nested Loop (cost=2.83..847.54 rows=1 width=192) (actual time=8.079..138,883.519 rows=6,723 loops=1)

  • Output: co.contractid, l.assetid, l.customerid, l.linkid, lt.name, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid
  • Inner Unique: true
  • Join Filter: (l.linktypeid = lt.linktypeid)
  • Rows Removed by Join Filter: 2,003
  • Buffers: shared hit=4,968,444 read=399,375 dirtied=549
  • I/O Timings: read=132,565.089
5. 886.132 138,862.261 ↓ 6,723.0 6,723 1

Nested Loop Left Join (cost=2.83..846.45 rows=1 width=78) (actual time=8.074..138,862.261 rows=6,723 loops=1)

  • Output: co.contractid, l.assetid, l.customerid, l.linkid, l.linktypeid, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid
  • Inner Unique: true
  • Join Filter: (ems.assetmlmodelid = mlmodel.assetmlmodelid)
  • Filter: (ems.scoring IS NULL)
  • Rows Removed by Filter: 861,231
  • Buffers: shared hit=4,961,721 read=399,375 dirtied=549
  • I/O Timings: read=132,565.089
6. 215.414 95,446.383 ↓ 72,329.5 867,954 1

Nested Loop (cost=2.27..839.06 rows=12 width=82) (actual time=1.003..95,446.383 rows=867,954 loops=1)

  • Output: co.contractid, mlmodel.assetmlmodelid, l.assetid, l.customerid, l.linkid, l.linktypeid, ei.extrainfoid, ei.stringvalue, ei.assetmatchingid
  • Buffers: shared hit=725,181 read=299,217 dirtied=369
  • I/O Timings: read=92,672.814
7. 39.522 166.483 ↓ 517.5 151,618 1

Nested Loop (cost=1.69..18.10 rows=293 width=28) (actual time=0.459..166.483 rows=151,618 loops=1)

  • Output: a.customerid, co.contractid, mlmodel.assetmlmodelid, l.assetid, l.customerid, l.linkid, l.linktypeid
  • Buffers: shared hit=18,462 read=384
  • I/O Timings: read=47.341
8. 0.002 0.431 ↑ 1.0 1 1

Nested Loop (cost=1.12..10.03 rows=1 width=12) (actual time=0.424..0.431 rows=1 loops=1)

  • Output: a.customerid, co.contractid, mlmodel.assetmlmodelid
  • Buffers: shared hit=11 read=1
  • I/O Timings: read=0.376
9. 0.001 0.035 ↑ 1.0 1 1

Nested Loop (cost=0.84..7.51 rows=1 width=8) (actual time=0.031..0.035 rows=1 loops=1)

  • Output: a.customerid, co.contractid
  • Inner Unique: true
  • Buffers: shared hit=9
10. 0.003 0.024 ↑ 1.0 1 1

Nested Loop (cost=0.57..5.01 rows=1 width=8) (actual time=0.019..0.024 rows=1 loops=1)

  • Output: a.customerid, coa.contractid
  • Buffers: shared hit=6
11. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_asset on rpcore.asset a (cost=0.29..2.50 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=1)

  • Output: a.customerid
  • Index Cond: (a.assetid = 72)
  • Filter: a.active
  • Buffers: shared hit=3
12. 0.011 0.011 ↑ 1.0 1 1

Index Scan using idx_contract_asset__asset on rpcore.contract_asset coa (cost=0.28..2.50 rows=1 width=4) (actual time=0.009..0.011 rows=1 loops=1)

  • Output: coa.contractassetid, coa.contractid, coa.assetid, coa.createdby, coa.createdat, coa.modifiedby, coa.modifiedat, coa.tenantid
  • Index Cond: (coa.assetid = 72)
  • Buffers: shared hit=3
13. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_contract on rpcore.contract co (cost=0.28..2.50 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: co.contractid
  • Index Cond: (co.contractid = coa.contractid)
  • Filter: co.active
  • Buffers: shared hit=3
14. 0.394 0.394 ↑ 1.0 1 1

Index Scan using assetmlmodel_active_nondeleted_assetid_modeltypeid on rpcore.assetmlmodel mlmodel (cost=0.28..2.50 rows=1 width=4) (actual time=0.392..0.394 rows=1 loops=1)

  • Output: mlmodel.assetmlmodelid
  • Index Cond: ((mlmodel.assetid = 72) AND (mlmodel.modeltypeid = 1))
  • Filter: ((mlmodel.itemspositive >= 200) AND (mlmodel.itemsnegative >= 200) AND (mlmodel.averageprecision >= '90'::numeric))
  • Buffers: shared hit=2 read=1
  • I/O Timings: read=0.376
15. 126.530 126.530 ↓ 856.6 151,618 1

Index Only Scan using link__cus_ass_link_lt_nodeleted on rpcore.link l (cost=0.56..6.30 rows=177 width=16) (actual time=0.034..126.530 rows=151,618 loops=1)

  • Output: l.customerid, l.assetid, l.linkid, l.linktypeid
  • Index Cond: ((l.customerid = a.customerid) AND (l.assetid = 72))
  • Heap Fetches: 285
  • Buffers: shared hit=18,451 read=383
  • I/O Timings: read=46.965
16. 95,064.486 95,064.486 ↓ 6.0 6 151,618

Index Scan using idx_extrainfo_linkid_customerid_extrainfotypeid on rpcore.extrainfo ei (cost=0.58..2.79 rows=1 width=66) (actual time=0.226..0.627 rows=6 loops=151,618)

  • Output: ei.extrainfoid, ei.stringvalue, ei.assetmatchingid, ei.linkid, ei.customerid
  • Index Cond: ((ei.linkid = l.linkid) AND (ei.customerid = l.customerid) AND (ei.extrainfotypeid = 1))
  • Filter: (ei.assetmatchingid = 1)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=706,719 read=298,833 dirtied=369
  • I/O Timings: read=92,625.473
17. 42,529.746 42,529.746 ↑ 1.0 1 867,954

Index Scan using idx_exinfmlmodelscoring_extrainfoid on rpcore.extrainfo_mlmodel_scoring ems (cost=0.57..0.60 rows=1 width=16) (actual time=0.049..0.049 rows=1 loops=867,954)

  • Output: ems.assetmlmodelid, ems.extrainfoid, ems.scoring
  • Index Cond: (ems.extrainfoid = ei.extrainfoid)
  • Buffers: shared hit=4,236,540 read=100,158 dirtied=180
  • I/O Timings: read=39,892.275
18. 6.723 6.723 ↑ 4.0 1 6,723

Seq Scan on rpcore.linktype lt (cost=0.00..1.04 rows=4 width=122) (actual time=0.001..0.001 rows=1 loops=6,723)

  • Output: lt.linktypeid, lt.name, lt.literalkey
  • Buffers: shared hit=6,723
19. 1,902.609 1,902.609 ↑ 1.0 1 6,723

Index Scan using idx_recognitionimagepredictionrequest_extrainfo on rpcore.recognitionimagepredictionrequest req (cost=0.57..0.60 rows=1 width=28) (actual time=0.283..0.283 rows=1 loops=6,723)

  • Output: req.extrainfoid, req.quantity, req.lastrequest, req.imagerecognitionresultstatusid, req.executiontypeid
  • Index Cond: (req.extrainfoid = ei.extrainfoid)
  • Buffers: shared hit=29,491 read=4,000 dirtied=6
  • I/O Timings: read=1,848.241
Planning time : 16.341 ms
Execution time : 140,814.007 ms