explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kvlg

Settings
# exclusive inclusive rows x rows loops node
1. 3.456 14,856.701 ↑ 1.0 1 1

Aggregate (cost=4,340,075.22..4,340,075.23 rows=1 width=8) (actual time=14,856.701..14,856.701 rows=1 loops=1)

2.          

CTE entity_raw

3. 21.985 346.124 ↑ 1.0 4,750 1

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

4. 315.837 324.139 ↑ 1.0 4,750 1

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

  • Sort Key: rp.works_ref, rp.date_received DESC
  • Sort Method: external merge Disk: 11944kB
5. 4.733 8.302 ↑ 1.0 4,750 1

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

6. 0.345 3.569 ↑ 1.0 4,750 1

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

7. 2.868 3.224 ↑ 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.514..3.224 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=1690
8. 0.356 0.356 ↑ 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.356..0.356 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. 380.581 380.581 ↓ 190.0 4,750 1

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

  • Filter: (rnk = 1)
11.          

CTE insert_entites

12. 1,051.484 14,847.609 ↑ 1,535.2 4,750 1

Insert on traffic_replay_entity_apps3034 (cost=105.64..3,893,987.37 rows=7,291,965 width=1,654) (actual time=434.571..14,847.609 rows=4,750 loops=1)

13. 24.259 13,796.125 ↑ 1,535.2 4,750 1

Subquery Scan on *SELECT* (cost=105.64..3,893,987.37 rows=7,291,965 width=1,654) (actual time=434.454..13,796.125 rows=4,750 loops=1)

14. 1,015.790 13,771.866 ↑ 1,535.2 4,750 1

Nested Loop Left Join (cost=105.64..3,766,377.99 rows=7,291,965 width=1,466) (actual time=434.452..13,771.866 rows=4,750 loops=1)

15. 14.184 458.326 ↓ 12.9 4,750 1

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

  • Hash Cond: (exref.tomtom_entity_id = e.entity_id)
16. 0.530 14.889 ↑ 1.0 2,802 1

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

17. 14.359 14.359 ↑ 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.758..14.359 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: 154878
18. 4.219 429.253 ↓ 190.0 4,750 1

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

  • Buckets: 4096 (originally 1024) Batches: 2 (originally 1) Memory Usage: 4065kB
19. 15.628 425.034 ↓ 190.0 4,750 1

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

  • Hash Cond: ((ecat.nsa_code)::text = (e.entity_category)::text)
20. 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)

21. 8.688 409.394 ↓ 190.0 4,750 1

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

  • Buckets: 4096 (originally 1024) Batches: 16 (originally 1) Memory Usage: 4191kB
22. 400.706 400.706 ↓ 190.0 4,750 1

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

23. 6.660 12,297.750 ↑ 2.0 1 4,750

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

24. 11.224 12,278.750 ↑ 2.0 1 4,750

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

25. 1,068.378 12,250.250 ↑ 2.0 1 4,750

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

26. 11,172.000 11,172.000 ↑ 2.0 1 4,750

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

27. 9.872 9.872 ↓ 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.004..0.004 rows=0 loops=2,468)

  • Index Cond: (archive_e.permit_status = permit_status)
28. 17.276 17.276 ↑ 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.007..0.007 rows=1 loops=2,468)

  • Index Cond: (archive_e.lha_id = la_id)
29. 12.340 12.340 ↑ 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.005..0.005 rows=1 loops=2,468)

  • Index Cond: ((archive_e.traffman_code = traffman_code) AND (eton_ver = '6'::double precision))
30.          

CTE insert_items

31. 227.518 4,707.581 ↑ 12,581.1 1,494 1

Insert on traffic_replay_item_apps3034 (cost=308.09..211,007.27 rows=18,796,183 width=1,254) (actual time=17.388..4,707.581 rows=1,494 loops=1)

32. 48.016 4,480.063 ↑ 12,581.1 1,494 1

Nested Loop Left Join (cost=308.09..211,007.27 rows=18,796,183 width=1,254) (actual time=17.338..4,480.063 rows=1,494 loops=1)

33. 4.024 23.253 ↓ 1.6 1,494 1

Hash Right Join (cost=207.10..1,719.29 rows=946 width=1,945) (actual time=13.833..23.253 rows=1,494 loops=1)

  • Hash Cond: (ixref.tomtom_item_id = i.item_id)
34. 0.238 6.005 ↑ 1.0 948 1

Append (cost=0.00..1,476.44 rows=956 width=16) (actual time=0.598..6.005 rows=948 loops=1)

35. 5.767 5.767 ↑ 1.0 948 1

Seq Scan on tomtom_item_mapping_elgin_ids_apps3034_20190827 ixref (cost=0.00..1,476.44 rows=956 width=16) (actual time=0.598..5.767 rows=948 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: 56157
36. 1.068 13.224 ↓ 7.5 1,494 1

Hash (cost=204.62..204.62 rows=198 width=1,937) (actual time=13.224..13.224 rows=1,494 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1448kB
37. 0.000 12.156 ↓ 7.5 1,494 1

Nested Loop (cost=0.29..204.62 rows=198 width=1,937) (actual time=0.397..12.156 rows=1,494 loops=1)

38. 3.543 3.543 ↓ 190.0 4,750 1

CTE Scan on first_ranked_entities e_1 (cost=0.00..0.50 rows=25 width=8) (actual time=0.013..3.543 rows=4,750 loops=1)

39. 4.750 9.500 ↓ 0.0 0 4,750

Append (cost=0.29..8.15 rows=1 width=1,937) (actual time=0.002..0.002 rows=0 loops=4,750)

40. 4.750 4.750 ↓ 0.0 0 4,750

Index Scan using idx_traffic_replay_raw_item_apps3034_20190827_ed on traffic_replay_raw_item_apps3034_20190827 i (cost=0.29..8.15 rows=1 width=1,937) (actual time=0.001..0.001 rows=0 loops=4,750)

  • Index Cond: (entity_id = e_1.entity_id)
  • 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))
41. 1.938 4,408.794 ↑ 2.0 1 1,494

Nested Loop Left Join (cost=100.99..121.87 rows=2 width=780) (actual time=2.950..2.951 rows=1 loops=1,494)

42. 4.320 4,404.312 ↑ 2.0 1 1,494

Nested Loop Left Join (cost=100.85..121.53 rows=2 width=664) (actual time=2.947..2.948 rows=1 loops=1,494)

43. 391.872 4,392.360 ↑ 2.0 1 1,494

Nested Loop Left Join (cost=100.58..117.26 rows=2 width=650) (actual time=2.940..2.940 rows=1 loops=1,494)

44. 3,997.944 3,997.944 ↑ 2.0 1 1,494

Foreign Scan on entity archive_e_1 (cost=100.44..116.95 rows=2 width=112) (actual time=2.676..2.676 rows=1 loops=1,494)

45. 2.544 2.544 ↓ 0.0 0 848

Index Scan using eton_permit_status_pkey on eton_permit_status archive_ps_1 (cost=0.14..0.16 rows=1 width=170) (actual time=0.003..0.003 rows=0 loops=848)

  • Index Cond: (archive_e_1.permit_status = permit_status)
46. 7.632 7.632 ↑ 1.0 1 848

Index Scan using la_pkey on la archive_la_1 (cost=0.27..2.13 rows=1 width=22) (actual time=0.009..0.009 rows=1 loops=848)

  • Index Cond: (archive_e_1.lha_id = la_id)
47. 2.544 2.544 ↓ 0.0 0 848

Index Scan using eton_trafficmanagement_pkey on eton_trafficmanagement archive_traffman_1 (cost=0.14..0.16 rows=1 width=120) (actual time=0.003..0.003 rows=0 loops=848)

  • Index Cond: ((archive_e_1.traffman_code = traffman_code) AND (eton_ver = '6'::double precision))
48. 14,853.245 14,853.245 ↑ 1,535.2 4,750 1

CTE Scan on insert_entites (cost=0.00..145,839.30 rows=7,291,965 width=0) (actual time=434.574..14,853.245 rows=4,750 loops=1)

Planning time : 102.504 ms
Execution time : 19,575.692 ms