explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tXcB

Settings
# exclusive inclusive rows x rows loops node
1. 0.089 3,319.877 ↑ 1.0 10 1

Limit (cost=1,325,380.38..1,325,382.85 rows=10 width=37) (actual time=3,319.780..3,319.877 rows=10 loops=1)

2.          

CTE cte_entity_agg

3. 117.553 691.212 ↑ 1.0 1,851,074 1

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

4. 573.659 573.659 ↑ 1.0 1,851,074 1

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

  • Filter: (partition_code = 2019)
5. 0.006 3,319.788 ↑ 21,134.4 10 1

Unique (cost=1,160,784.61..1,213,058.23 rows=211,344 width=37) (actual time=3,319.778..3,319.788 rows=10 loops=1)

6. 462.712 3,319.782 ↑ 298,706.4 10 1

Sort (cost=1,160,784.61..1,168,252.27 rows=2,987,064 width=37) (actual time=3,319.777..3,319.782 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
7. 451.518 2,857.070 ↑ 39.1 76,325 1

Hash Right Join (cost=581,152.25..676,162.44 rows=2,987,064 width=37) (actual time=2,075.509..2,857.070 rows=76,325 loops=1)

  • Hash Cond: (ea.entity_id = e.entity_id)
8. 1,139.151 1,139.151 ↑ 1.0 1,851,074 1

CTE Scan on cte_entity_agg ea (cost=0.00..37,054.04 rows=1,852,702 width=8) (actual time=0.016..1,139.151 rows=1,851,074 loops=1)

9. 46.309 1,266.401 ↑ 1.6 75,616 1

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

  • Buckets: 16384 Batches: 16 Memory Usage: 523kB
10. 22.456 1,220.092 ↑ 1.6 75,616 1

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

  • Workers Planned: 2
  • Workers Launched: 2
11. 242.868 1,197.636 ↑ 2.0 25,205 3

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

12. 11.307 954.759 ↑ 1.3 23,440 3

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

  • Hash Cond: ((e.works_ref)::text = (w.works_ref)::text)
13. 2.820 943.435 ↑ 1.3 23,440 3

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

14. 940.615 940.615 ↑ 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=746.878..940.615 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
15. 0.000 0.017 ↓ 0.0 0 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.017 0.017 ↓ 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.017..0.017 rows=0 loops=3)

17. 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.009..0.009 rows=1 loops=70,319)

  • Index Cond: (e.entity_id = entity_id)
Planning time : 4.311 ms
Execution time : 3,327.774 ms