explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XNTh

Settings
# exclusive inclusive rows x rows loops node
1. 16,046.214 16,046.214 ↑ 1,535.2 4,750 1

CTE Scan on insert_entites (cost=3,855,647.10..4,001,486.40 rows=7,291,965 width=1,687) (actual time=463.396..16,046.214 rows=4,750 loops=1)

2.          

CTE entity_raw

3. 21.851 343.865 ↑ 1.0 4,750 1

WindowAgg (cost=70,796.31..70,894.57 rows=4,913 width=13,006) (actual time=266.173..343.865 rows=4,750 loops=1)

4. 313.316 322.014 ↑ 1.0 4,750 1

Sort (cost=70,796.31..70,808.59 rows=4,913 width=12,998) (actual time=266.162..322.014 rows=4,750 loops=1)

  • Sort Key: rp.works_ref, rp.date_received DESC
  • Sort Method: external merge Disk: 11,944kB
5. 4.781 8.698 ↑ 1.0 4,750 1

Result (cost=122.78..15,818.08 rows=4,913 width=12,998) (actual time=0.580..8.698 rows=4,750 loops=1)

6. 0.357 3.917 ↑ 1.0 4,750 1

Append (cost=122.78..15,768.95 rows=4,913 width=12,998) (actual time=0.577..3.917 rows=4,750 loops=1)

7. 3.162 3.560 ↑ 1.0 4,750 1

Bitmap Heap Scan on traffic_replay_raw_entity_apps3034_20190827 rp (cost=122.78..15,768.95 rows=4,913 width=12,998) (actual time=0.576..3.560 rows=4,750 loops=1)

  • Recheck Cond: ((date_received >= '2019-08-27 22:45:00'::timestamp without time zone) AND (date_received <= '2019-08-27 22:49:59'::timestamp without time zone))
  • Heap Blocks: exact=1,690
8. 0.398 0.398 ↑ 1.0 4,750 1

Bitmap Index Scan on idx_traffic_replay_raw_entity_apps3034_20190827_dr (cost=0.00..121.55 rows=4,913 width=0) (actual time=0.397..0.398 rows=4,750 loops=1)

  • Index Cond: ((date_received >= '2019-08-27 22:45:00'::timestamp without time zone) AND (date_received <= '2019-08-27 22:49:59'::timestamp without time zone))
9.          

CTE first_ranked_entities

10. 378.290 378.290 ↓ 190.0 4,750 1

CTE Scan on entity_raw (cost=0.00..116.79 rows=25 width=7,388) (actual time=266.218..378.290 rows=4,750 loops=1)

  • Filter: (rnk = 1)
11.          

CTE insert_entites

12. 1,394.499 15,985.497 ↑ 1,535.2 4,750 1

Nested Loop Left Join (cost=105.64..3,784,635.74 rows=7,291,965 width=1,466) (actual time=463.391..15,985.497 rows=4,750 loops=1)

13. 8.176 473.998 ↓ 12.9 4,750 1

Nested Loop Left Join (cost=4.65..4,123.56 rows=367 width=836) (actual time=429.897..473.998 rows=4,750 loops=1)

  • Join Filter: (e.impact_score = imps.impact_score)
  • Rows Removed by Join Filter: 11,660
14. 17.477 461.072 ↓ 12.9 4,750 1

Hash Right Join (cost=4.65..4,095.72 rows=367 width=630) (actual time=429.887..461.072 rows=4,750 loops=1)

  • Hash Cond: (exref.tomtom_entity_id = e.entity_id)
15. 0.631 15.611 ↑ 1.0 2,802 1

Append (cost=0.00..4,076.38 rows=2,937 width=16) (actual time=1.851..15.611 rows=2,802 loops=1)

16. 14.980 14.980 ↑ 1.0 2,802 1

Seq Scan on tomtom_entity_mapping_elgin_ids_apps3034_20190827 exref (cost=0.00..4,076.38 rows=2,937 width=16) (actual time=1.850..14.980 rows=2,802 loops=1)

  • Filter: ((date_received >= '2019-08-27 22:45:00'::timestamp without time zone) AND (date_received <= '2019-08-27 22:49:59'::timestamp without time zone) AND (mapping_order = 1))
  • Rows Removed by Filter: 154,878
17. 4.678 427.984 ↓ 190.0 4,750 1

Hash (cost=4.34..4.34 rows=25 width=622) (actual time=427.984..427.984 rows=4,750 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 2 (originally 1) Memory Usage: 4,065kB
18. 15.918 423.306 ↓ 190.0 4,750 1

Hash Right Join (cost=0.81..4.34 rows=25 width=622) (actual time=407.422..423.306 rows=4,750 loops=1)

  • Hash Cond: ((ecat.nsa_code)::text = (e.entity_category)::text)
19. 0.012 0.012 ↑ 1.0 93 1

Seq Scan on entity_category ecat (cost=0.00..2.93 rows=93 width=10) (actual time=0.005..0.012 rows=93 loops=1)

20. 8.962 407.376 ↓ 190.0 4,750 1

Hash (cost=0.50..0.50 rows=25 width=619) (actual time=407.376..407.376 rows=4,750 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 16 (originally 1) Memory Usage: 4,191kB
21. 398.414 398.414 ↓ 190.0 4,750 1

CTE Scan on first_ranked_entities e (cost=0.00..0.50 rows=25 width=619) (actual time=266.227..398.414 rows=4,750 loops=1)

22. 4.745 4.750 ↑ 1.7 3 4,750

Materialize (cost=0.00..1.07 rows=5 width=210) (actual time=0.000..0.001 rows=3 loops=4,750)

23. 0.005 0.005 ↑ 1.2 4 1

Seq Scan on eton_impact imps (cost=0.00..1.05 rows=5 width=210) (actual time=0.003..0.005 rows=4 loops=1)

24. 11.224 14,117.000 ↑ 2.0 1 4,750

Nested Loop Left Join (cost=100.99..118.18 rows=2 width=780) (actual time=2.971..2.972 rows=1 loops=4,750)

25. 9.328 14,088.500 ↑ 2.0 1 4,750

Nested Loop Left Join (cost=100.85..117.84 rows=2 width=664) (actual time=2.966..2.966 rows=1 loops=4,750)

26. 932.724 13,822.500 ↑ 2.0 1 4,750

Nested Loop Left Join (cost=100.58..117.26 rows=2 width=650) (actual time=2.909..2.910 rows=1 loops=4,750)

27. 12,872.500 12,872.500 ↑ 2.0 1 4,750

Foreign Scan on entity archive_e (cost=100.44..116.95 rows=2 width=112) (actual time=2.710..2.710 rows=1 loops=4,750)

28. 17.276 17.276 ↓ 0.0 0 2,468

Index Scan using eton_permit_status_pkey on eton_permit_status archive_ps (cost=0.14..0.16 rows=1 width=170) (actual time=0.007..0.007 rows=0 loops=2,468)

  • Index Cond: (archive_e.permit_status = permit_status)
29. 256.672 256.672 ↑ 1.0 1 2,468

Index Scan using la_pkey on la archive_la (cost=0.27..0.29 rows=1 width=22) (actual time=0.104..0.104 rows=1 loops=2,468)

  • Index Cond: (archive_e.lha_id = la_id)
30. 17.276 17.276 ↑ 1.0 1 2,468

Index Scan using eton_trafficmanagement_pkey on eton_trafficmanagement archive_traffman (cost=0.14..0.16 rows=1 width=120) (actual time=0.007..0.007 rows=1 loops=2,468)

  • Index Cond: ((archive_e.traffman_code = traffman_code) AND (eton_ver = '6'::double precision))
Planning time : 47.019 ms
Execution time : 16,056.166 ms