explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TPoE

Settings
# exclusive inclusive rows x rows loops node
1. 0.096 2,816.193 ↑ 1.0 10 1

Limit (cost=1,288,326.34..1,288,328.81 rows=10 width=37) (actual time=2,816.088..2,816.193 rows=10 loops=1)

2. 0.007 2,816.097 ↑ 21,134.4 10 1

Unique (cost=1,288,326.34..1,340,599.96 rows=211,344 width=37) (actual time=2,816.086..2,816.097 rows=10 loops=1)

3. 476.006 2,816.090 ↑ 298,706.4 10 1

Sort (cost=1,288,326.34..1,295,794.00 rows=2,987,064 width=37) (actual time=2,816.085..2,816.090 rows=10 loops=1)

  • Sort Key: e.works_ref, e.start_date, e.end_date, (CASE WHEN (w.works_ref IS NULL) THEN true ELSE false END), (CASE WHEN (ec.entity_id IS NULL) THEN true ELSE false END), (CASE WHEN ((ea.entity_id IS NULL) AND (ec.location_point_easting IS NOT NULL)) THEN true ELSE false END)
  • Sort Method: external merge Disk: 3656kB
4. 462.505 2,340.084 ↑ 39.1 76,325 1

Hash Right Join (cost=581,152.25..803,704.18 rows=2,987,064 width=37) (actual time=1,777.568..2,340.084 rows=76,325 loops=1)

  • Hash Cond: (ea.entity_id = e.entity_id)
5. 117.598 681.309 ↑ 1.0 1,851,074 1

Append (cost=0.00..164,595.77 rows=1,852,702 width=8) (actual time=0.011..681.309 rows=1,851,074 loops=1)

6. 563.711 563.711 ↑ 1.0 1,851,074 1

Seq Scan on entity_agg__startend_2019 ea (cost=0.00..164,595.77 rows=1,852,702 width=8) (actual time=0.010..563.711 rows=1,851,074 loops=1)

  • Filter: (partition_code = 2019)
7. 67.362 1,196.270 ↑ 1.6 75,616 1

Hash (cost=575,302.93..575,302.93 rows=120,266 width=272) (actual time=1,196.270..1,196.270 rows=75,616 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 523kB
8. 41.324 1,128.908 ↑ 1.6 75,616 1

Gather (cost=1,017.99..575,302.93 rows=120,266 width=272) (actual time=716.873..1,128.908 rows=75,616 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 224.294 1,087.584 ↑ 2.0 25,205 3

Nested Loop Left Join (cost=17.99..562,276.33 rows=50,111 width=272) (actual time=687.725..1,087.584 rows=25,205 loops=3)

10. 19.571 863.281 ↑ 1.3 23,440 3

Hash Left Join (cost=17.43..219,125.55 rows=31,081 width=260) (actual time=687.693..863.281 rows=23,440 loops=3)

  • Hash Cond: ((e.works_ref)::text = (w.works_ref)::text)
11. 3.623 843.694 ↑ 1.3 23,440 3

Append (cost=0.00..219,026.52 rows=31,081 width=42) (actual time=687.520..843.694 rows=23,440 loops=3)

12. 840.071 840.071 ↑ 1.3 23,440 3

Parallel Seq Scan on entity__startend_2019 e (cost=0.00..219,026.52 rows=31,081 width=42) (actual time=687.519..840.071 rows=23,440 loops=3)

  • Filter: (((entitygroupid)::text = 'RW'::text) AND (partition_code = 2019) AND (entity_type = ANY ('{1,3,4,5,6}'::integer[])) AND (archive_date >= ((CURRENT_DATE)::timestamp without time zone - '20 days'::interval)) AND (archive_date <= ((CURRENT_DATE)::timestamp without time zone - '6 days'::interval)))
  • Rows Removed by Filter: 620422
13. 0.000 0.016 ↓ 0.0 0 3

Hash (cost=13.30..13.30 rows=330 width=218) (actual time=0.016..0.016 rows=0 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
14. 0.016 0.016 ↓ 0.0 0 3

Seq Scan on entity_control_intgrity_check_worksref w (cost=0.00..13.30 rows=330 width=218) (actual time=0.016..0.016 rows=0 loops=3)

15. 0.009 0.009 ↑ 2.0 1 70,319

Index Scan using idx_entity_control_entity_id on entity_control ec (cost=0.56..11.02 rows=2 width=12) (actual time=0.008..0.009 rows=1 loops=70,319)

  • Index Cond: (e.entity_id = entity_id)
Planning time : 4.279 ms
Execution time : 2,817.027 ms