explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nsWc

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 1,840,959.780 ↑ 2.3 22 1

Limit (cost=45,659.69..46,622.10 rows=50 width=2,253) (actual time=443,897.378..1,840,959.780 rows=22 loops=1)

  • Buffers: shared hit=22,220,019 read=19,017,163
2.          

CTE lead_hits

3. 5,134.847 5,335.886 ↓ 5.5 4,196 1

Bitmap Heap Scan on detection_leads (cost=545.68..45,631.82 rows=760 width=16) (actual time=202.762..5,335.886 rows=4,196 loops=1)

  • Recheck Cond: ((lead_value = ANY ('{p:1,780f8c4141be3ee0f1f0554466e8325,p:212dcda500adb911fc761efdcc86275d,p:4f86203d3ea9222126c33b8af3e45762,p:68bd5061237365dae2786ced1f37537b,p:76bd9a5495e48b1b38cd6dde8c4c688b,p:ead980b5d1c5db5dc3f2a314587463be}'::text[])) AND (lead_type = 'PROCESS_GID'::enum_detection_leads_lead_type))
  • Filter: (organization = 'euronet'::text)
  • Heap Blocks: exact=4,629
  • Buffers: shared hit=35 read=4,760
4. 201.039 201.039 ↑ 1.9 5,910 1

Bitmap Index Scan on detection_leads_process_gid (cost=0.00..545.49 rows=11,475 width=0) (actual time=201.039..201.039 rows=5,910 loops=1)

  • Index Cond: (lead_value = ANY ('{p:1,780f8c4141be3ee0f1f0554466e8325,p:212dcda500adb911fc761efdcc86275d,p:4f86203d3ea9222126c33b8af3e45762,p:68bd5061237365dae2786ced1f37537b,p:76bd9a5495e48b1b38cd6dde8c4c688b,p:ead980b5d1c5db5dc3f2a314587463be}'::text[]))
  • Buffers: shared hit=6 read=160
5. 0.210 1,840,959.762 ↑ 194,317.3 22 1

GroupAggregate (cost=27.87..82,285,150.67 rows=4,274,981 width=2,253) (actual time=443,897.376..1,840,959.762 rows=22 loops=1)

  • Group Key: incidents.incident_id
  • Filter: (count(*) < 800)
  • Buffers: shared hit=22,220,019 read=19,017,163
6. 4,487.138 1,840,959.552 ↑ 51,794.9 114 1

Merge Left Join (cost=27.87..82,198,116.25 rows=5,904,615 width=2,245) (actual time=204,430.589..1,840,959.552 rows=114 loops=1)

  • Merge Cond: (incidents.incident_id = detections.incident_id)
  • Buffers: shared hit=22,220,019 read=19,017,163
7. 28.146 1,588,051.388 ↑ 97,158.6 22 1

Merge Join (cost=27.31..76,781,812.00 rows=2,137,490 width=2,245) (actual time=169,672.145..1,588,051.388 rows=22 loops=1)

  • Merge Cond: (incidents.incident_id = lead_hits.incident_id)
  • Buffers: shared hit=7,347 read=18,295,433
8. 1,582,675.410 1,582,675.410 ↑ 335.8 12,730 1

Index Scan using incidents_pkey on incidents (cost=0.56..76,771,096.42 rows=4,274,981 width=2,245) (actual time=158.562..1,582,675.410 rows=12,730 loops=1)

  • Filter: ((resolution <> 'MERGED'::text) AND (incident_type <> ALL ('{MANUAL,CUSTOMER}'::text[])) AND ((organization)::text = 'euronet'::text))
  • Rows Removed by Filter: 22,739,491
  • Buffers: shared hit=7,312 read=18,290,673
9. 3.110 5,347.832 ↓ 7.9 1,576 1

Sort (cost=26.74..27.24 rows=200 width=16) (actual time=5,345.174..5,347.832 rows=1,576 loops=1)

  • Sort Key: lead_hits.incident_id
  • Sort Method: quicksort Memory: 122kB
  • Buffers: shared hit=35 read=4,760
10. 4.375 5,344.722 ↓ 7.9 1,576 1

HashAggregate (cost=17.10..19.10 rows=200 width=16) (actual time=5,344.522..5,344.722 rows=1,576 loops=1)

  • Group Key: lead_hits.incident_id
  • Buffers: shared hit=35 read=4,760
11. 5,340.347 5,340.347 ↓ 5.5 4,196 1

CTE Scan on lead_hits (cost=0.00..15.20 rows=760 width=16) (actual time=202.764..5,340.347 rows=4,196 loops=1)

  • Buffers: shared hit=35 read=4,760
12. 248,421.026 248,421.026 ↑ 1.0 62,192,023 1

Index Only Scan using detection_incident_id_index on detections (cost=0.56..5,195,090.06 rows=62,729,736 width=16) (actual time=78.813..248,421.026 rows=62,192,023 loops=1)

  • Heap Fetches: 1,256,314
  • Buffers: shared hit=22,212,672 read=721,730
Planning time : 0.541 ms
Execution time : 1,840,964.950 ms