explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9dcA

Settings
# exclusive inclusive rows x rows loops node
1. 1.293 1,506.606 ↑ 1.0 1 1

Aggregate (cost=77,058.42..77,058.43 rows=1 width=8) (actual time=1,506.606..1,506.606 rows=1 loops=1)

2.          

CTE entity_raw

3. 22.728 346.374 ↑ 1.0 4,750 1

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

4. 315.375 323.646 ↑ 1.0 4,750 1

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

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

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

6. 0.327 3.629 ↑ 1.0 4,750 1

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

7. 2.919 3.302 ↑ 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.541..3.302 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.383 0.383 ↑ 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.382..0.383 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. 381.425 381.425 ↓ 190.0 4,750 1

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

  • Filter: (rnk = 1)
11.          

CTE insert_entites

12. 674.464 1,502.996 ↓ 12.9 4,750 1

Insert on traffic_replay_entity_apps3034 (cost=4.65..4,317.15 rows=367 width=924) (actual time=433.907..1,502.996 rows=4,750 loops=1)

13. 2.716 828.532 ↓ 12.9 4,750 1

Subquery Scan on *SELECT* (cost=4.65..4,317.15 rows=367 width=924) (actual time=433.806..828.532 rows=4,750 loops=1)

14. 374.034 825.816 ↓ 12.9 4,750 1

Nested Loop Left Join (cost=4.65..4,311.64 rows=367 width=694) (actual time=433.805..825.816 rows=4,750 loops=1)

  • Join Filter: (e.impact_score = imps.impact_score)
  • Rows Removed by Join Filter: 11660
15. 6.830 451.782 ↓ 12.9 4,750 1

Hash Right Join (cost=4.65..4,095.72 rows=367 width=622) (actual time=433.595..451.782 rows=4,750 loops=1)

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

Append (cost=0.00..4,076.38 rows=2,937 width=8) (actual time=1.867..13.255 rows=2,802 loops=1)

17. 13.011 13.011 ↑ 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=8) (actual time=1.867..13.011 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.808 431.697 ↓ 190.0 4,750 1

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

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

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

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

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

21. 9.020 411.038 ↓ 190.0 4,750 1

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

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

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

23. 0.000 0.000 ↑ 1.7 3 4,750

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

24. 0.003 0.003 ↑ 1.2 4 1

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

25.          

CTE insert_items

26. 77.015 105.745 ↓ 1.6 1,494 1

Insert on traffic_replay_item_apps3034 (cost=207.10..1,721.66 rows=946 width=524) (actual time=13.413..105.745 rows=1,494 loops=1)

27. 11.327 28.730 ↓ 1.6 1,494 1

Hash Right Join (cost=207.10..1,721.66 rows=946 width=524) (actual time=13.348..28.730 rows=1,494 loops=1)

  • Hash Cond: (ixref.tomtom_item_id = i.item_id)
28. 0.070 4.667 ↑ 1.0 948 1

Append (cost=0.00..1,476.44 rows=956 width=8) (actual time=0.594..4.667 rows=948 loops=1)

29. 4.597 4.597 ↑ 1.0 948 1

Seq Scan on tomtom_item_mapping_elgin_ids_apps3034_20190827 ixref (cost=0.00..1,476.44 rows=956 width=8) (actual time=0.594..4.597 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
30. 1.009 12.736 ↓ 7.5 1,494 1

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

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

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

32. 3.473 3.473 ↓ 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.030..3.473 rows=4,750 loops=1)

33. 4.750 9.500 ↓ 0.0 0 4,750

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

34. 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))
35. 1,505.313 1,505.313 ↓ 12.9 4,750 1

CTE Scan on insert_entites (cost=0.00..7.34 rows=367 width=0) (actual time=433.909..1,505.313 rows=4,750 loops=1)

Planning time : 59.567 ms
Execution time : 1,622.397 ms