explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rU8Q

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 2,446.987 ↓ 20.0 20 1

Limit (cost=3,423.06..3,423.1 rows=1 width=586) (actual time=2,446.967..2,446.987 rows=20 loops=1)

  • Buffers: shared hit=2231028
2. 0.020 2,446.975 ↓ 21.0 21 1

Aggregate (cost=3,423.02..3,423.06 rows=1 width=586) (actual time=2,446.964..2,446.975 rows=21 loops=1)

  • Buffers: shared hit=2231028
3. 1.798 2,446.955 ↓ 23.0 23 1

Sort (cost=3,423.02..3,423.02 rows=1 width=586) (actual time=2,446.953..2,446.955 rows=23 loops=1)

  • Sort Key: dl.highrisk DESC, dl.createdat, l.linkid, d.url, (COALESCE(cfg.icon, mcfg.icon)), (COALESCE(cfg.channelid, mcfg.channelid)), a.assetid, l.reopenfromlinkid
  • Sort Method: quicksort Memory: 213kB
  • Buffers: shared hit=2231028
4. 0.685 2,445.157 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.3..3,423.01 rows=1 width=586) (actual time=0.402..2,445.157 rows=1,168 loops=1)

  • Filter: (((c.customerdivisionid IS NOT NULL) AND (dc.domainid IS NOT NULL) AND (cc.scopeid IS NOT NULL) AND (ccd.contractid IS NOT NULL)) OR (c.customerdivisionid IS NULL))
  • Buffers: shared hit=2231028
5. 1.279 2,443.304 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.15..3,422.52 rows=1 width=198) (actual time=0.387..2,443.304 rows=1,168 loops=1)

  • Buffers: shared hit=2229860
6. 0.470 2,442.025 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.01..3,421.46 rows=1 width=198) (actual time=0.376..2,442.025 rows=1,168 loops=1)

  • Buffers: shared hit=2229860
7. 0.571 2,441.555 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.01..3,409.71 rows=1 width=198) (actual time=0.374..2,441.555 rows=1,168 loops=1)

  • Buffers: shared hit=2229860
8. 1.584 2,440.984 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.01..3,398.59 rows=1 width=198) (actual time=0.371..2,440.984 rows=1,168 loops=1)

  • Buffers: shared hit=2229860
9. 0.586 2,439.400 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.72..3,392.44 rows=1 width=144) (actual time=0.363..2,439.4 rows=1,168 loops=1)

  • Buffers: shared hit=2228996
10. 1.165 2,437.646 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.43..3,392.03 rows=1 width=148) (actual time=0.355..2,437.646 rows=1,168 loops=1)

  • Buffers: shared hit=2228132
11. 0.356 2,435.313 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.14..3,391.66 rows=1 width=90) (actual time=0.343..2,435.313 rows=1,168 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=2225492
12. 0.946 2,432.617 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.71..3,384.52 rows=1 width=98) (actual time=0.332..2,432.617 rows=1,170 loops=1)

  • Buffers: shared hit=2223978
13. 0.732 2,428.161 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.27..3,381.94 rows=1 width=93) (actual time=0.317..2,428.161 rows=1,170 loops=1)

  • Buffers: shared hit=2219282
14. 0.509 2,425.089 ↓ 1,170.0 1,170 1

Nested Loop (cost=1.98..3,381.63 rows=1 width=65) (actual time=0.304..2,425.089 rows=1,170 loops=1)

  • Buffers: shared hit=2215772
15. 189.521 2,422.240 ↓ 1,170.0 1,170 1

Nested Loop (cost=1.71..3,373.32 rows=1 width=57) (actual time=0.292..2,422.24 rows=1,170 loops=1)

  • Buffers: shared hit=2212262
16. 50.250 322.943 ↓ 3,978.7 318,296 1

Nested Loop (cost=1.28..3,240.33 rows=80 width=49) (actual time=0.081..322.943 rows=318,296 loops=1)

  • Buffers: shared hit=9176
17. 0.006 0.261 ↓ 8.0 8 1

Nested Loop (cost=0.85..52.85 rows=1 width=33) (actual time=0.052..0.261 rows=8 loops=1)

  • Buffers: shared hit=64
18. 0.070 0.070 ↑ 1.0 1 1

Seq Scan on scope scope (cost=0..23.08 rows=1 width=0) (actual time=0.007..0.07 rows=1 loops=1)

  • Filter: (scope.scopeid = 1)
  • Buffers: shared hit=12
19. 0.033 0.185 ↓ 8.0 8 1

Nested Loop (cost=0.85..29.76 rows=1 width=33) (actual time=0.044..0.185 rows=8 loops=1)

  • Buffers: shared hit=52
20. 0.039 0.112 ↓ 4.0 8 1

Nested Loop (cost=0.57..29.03 rows=2 width=29) (actual time=0.031..0.112 rows=8 loops=1)

  • Buffers: shared hit=28
21. 0.033 0.033 ↓ 4.0 8 1

Index Scan using idx_asset_customer on asset a (cost=0.28..12.41 rows=2 width=21) (actual time=0.018..0.033 rows=8 loops=1)

  • Index Cond: (a.customerid = 594)
  • Filter: a.active
  • Buffers: shared hit=4
22. 0.040 0.040 ↑ 1.0 1 8

Index Only Scan using uq_asset_scope on asset_scope ascope (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=8)

  • Index Cond: ((ascope.assetid = a.assetid) AND (ascope.scopeid = 1))
  • Heap Fetches: 8
  • Buffers: shared hit=24
23. 0.040 0.040 ↑ 1.0 1 8

Index Scan using idx_contract_asset__asset on contract_asset coa (cost=0.28..0.36 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=8)

  • Index Cond: (coa.assetid = ascope.assetid)
  • Filter: (coa.contractid = 614)
  • Buffers: shared hit=24
24. 28.824 272.432 ↑ 1.1 39,787 8

Append (cost=0.43..2,653.65 rows=42,706 width=16) (actual time=0.014..34.054 rows=39,787 loops=8)

  • Buffers: shared hit=9112
25. 243.608 243.608 ↑ 1.1 39,787 8

Index Scan using link_partitioned_594_customerid_assetid_linkid_linktypeid_e_idx on link_partitioned_594 l (cost=0.43..2,440.12 rows=42,706 width=16) (actual time=0.012..30.451 rows=39,787 loops=8)

  • Index Cond: ((l.customerid = 594) AND (l.assetid = ascope.assetid) AND (l.linktypeid = 1) AND (l.enforcementstatusid = 6))
  • Buffers: shared hit=9112
26. 0.000 1,909.776 ↓ 0.0 0 318,296

Append (cost=0.43..1.65 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=318,296)

  • Buffers: shared hit=2203086
27. 1,909.776 1,909.776 ↓ 0.0 0 318,296

Index Scan using incident_partitioned_594_customerid_detectionlinkid_targetd_key on incident_partitioned_594 i (cost=0.43..1.64 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=318,296)

  • Index Cond: ((i.customerid = 594) AND (i.detectionlinkid = l.linkid))
  • Filter: ((NOT i.deleted) AND (i.contractid = 614) AND (i.incidentstatusid = 2) AND (i.enforcementlevelid = 8) AND (l.assetid = i.assetid))
  • Buffers: shared hit=2203086
28. 2.340 2.340 ↑ 1.0 1 1,170

Index Scan using pk_contract on contract co (cost=0.28..8.29 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1,170)

  • Index Cond: (co.contractid = 614)
  • Filter: co.active
  • Buffers: shared hit=3510
29. 2.340 2.340 ↑ 1.0 1 1,170

Index Scan using uq_domain_domainid_domaintypeid on domain d (cost=0.29..0.32 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1,170)

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=3510
30. 3.510 3.510 ↑ 1.0 1 1,170

Index Scan using idx_detection_link on detectionlink dl (cost=0.43..2.58 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=1,170)

  • Index Cond: ((dl.detectionlinkid = l.linkid) AND (dl.linktypeid = 1))
  • Buffers: shared hit=4696
31. 1.170 2.340 ↓ 0.0 0 1,170

Append (cost=0.43..7.13 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=1,170)

  • Buffers: shared hit=1514
32. 1.170 1.170 ↓ 0.0 0 1,170

Index Scan using link_partitioned_594_customerid_assetid_linkid_linktypeid_idx on link_partitioned_594 cl (cost=0.43..7.13 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=1,170)

  • Index Cond: ((cl.customerid = 594) AND (cl.assetid = l.assetid) AND (cl.linkid = i.contentlinkid) AND (cl.linktypeid = 2))
  • Buffers: shared hit=1514
33. 1.168 1.168 ↑ 1.0 1 1,168

Index Scan using pk_domainconfiguration on domainconfiguration cfg (cost=0.29..0.37 rows=1 width=62) (actual time=0.001..0.001 rows=1 loops=1,168)

  • Index Cond: (cfg.domainconfigurationid = d.domainconfigurationid)
  • Buffers: shared hit=2640
34. 1.168 1.168 ↓ 0.0 0 1,168

Index Scan using uq_domain_domainid_domaintypeid on domain md (cost=0.29..0.4 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,168)

  • Index Cond: (md.domainid = d.masterdomainid)
  • Filter: (md.hierarchylevelid = 1)
  • Buffers: shared hit=864
35. 0.000 0.000 ↓ 0.0 0 1,168

Index Scan using pk_domainconfiguration on domainconfiguration mcfg (cost=0.29..6.15 rows=1 width=62) (actual time=0..0 rows=0 loops=1,168)

  • Index Cond: (mcfg.domainconfigurationid = md.domainconfigurationid)
  • Buffers: shared hit=864
36. 0.000 0.000 ↓ 0.0 0 1,168

Seq Scan on customerdivision c (cost=0..10.5 rows=50 width=8) (actual time=0..0 rows=0 loops=1,168)

37. 0.000 0.000 ↓ 0.0 0 1,168

Seq Scan on domain_customerdivision dc (cost=0..10.7 rows=70 width=8) (actual time=0..0 rows=0 loops=1,168)

38. 0.000 0.000 ↓ 0.0 0 1,168

Index Scan using idx_contractcustomerdivision_customerdivision on contract_customerdivision ccd (cost=0.14..1.04 rows=1 width=8) (actual time=0..0 rows=0 loops=1,168)

  • Index Cond: (ccd.customerdivisionid = c.customerdivisionid)
  • Filter: (ccd.contractid = 614)
39. 1.168 1.168 ↓ 0.0 0 1,168

Index Scan using idx_customerdivisionscope_scope on customerdivision_scope cc (cost=0.15..0.36 rows=10 width=8) (actual time=0.001..0.001 rows=0 loops=1,168)

  • Index Cond: ((cc.scopeid = ascope.scopeid) AND (cc.scopeid = 1))
  • Buffers: shared hit=1168
Planning time : 62.562 ms
Execution time : 2,447.234 ms