explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PicK

Settings
# exclusive inclusive rows x rows loops node
1. 787.376 787.376 ↓ 12.9 4,750 1

CTE Scan on insert_entites (cost=75,323.00..75,330.34 rows=367 width=715) (actual time=423.585..787.376 rows=4,750 loops=1)

2.          

CTE entity_raw

3. 21.528 340.338 ↑ 1.0 4,750 1

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

4. 310.314 318.810 ↑ 1.0 4,750 1

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

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

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

6. 0.339 3.565 ↑ 1.0 4,750 1

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

7. 2.872 3.226 ↑ 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.513..3.226 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.354 0.354 ↑ 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.354..0.354 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. 373.938 373.938 ↓ 190.0 4,750 1

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

  • Filter: (rnk = 1)
11.          

CTE insert_entites

12. 326.807 766.993 ↓ 12.9 4,750 1

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

  • Join Filter: (e.impact_score = imps.impact_score)
  • Rows Removed by Join Filter: 11660
13. 5.260 440.186 ↓ 12.9 4,750 1

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

  • Hash Cond: (exref.tomtom_entity_id = e.entity_id)
14. 0.224 13.360 ↑ 1.0 2,802 1

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

15. 13.136 13.136 ↑ 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.775..13.136 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
16. 4.431 421.566 ↓ 190.0 4,750 1

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

  • Buckets: 4096 (originally 1024) Batches: 2 (originally 1) Memory Usage: 4065kB
17. 14.865 417.135 ↓ 190.0 4,750 1

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

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

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

19. 8.894 402.256 ↓ 190.0 4,750 1

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

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

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

21. 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)

22. 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.002..0.003 rows=4 loops=1)

Planning time : 20.293 ms
Execution time : 795.873 ms