explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PFlx

Settings
# exclusive inclusive rows x rows loops node
1. 1.307 1,478.351 ↑ 1.0 1 1

Aggregate (cost=71,234.51..71,234.52 rows=1 width=8) (actual time=1,478.351..1,478.351 rows=1 loops=1)

2.          

CTE entity_raw

3. 22.077 347.769 ↑ 1.0 4,750 1

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

4. 317.351 325.692 ↑ 1.0 4,750 1

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

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

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

6. 0.346 3.593 ↑ 1.0 4,750 1

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

7. 2.889 3.247 ↑ 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.517..3.247 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.358 0.358 ↑ 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.358..0.358 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.890 381.890 ↓ 190.0 4,750 1

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

  • Filter: (rnk = 1)
11.          

CTE insert_entites

12. 680.553 1,474.577 ↓ 190.0 4,750 1

Insert on traffic_replay_entity_apps3034 (cost=0.81..17.47 rows=25 width=924) (actual time=411.226..1,474.577 rows=4,750 loops=1)

13. 2.581 794.024 ↓ 190.0 4,750 1

Subquery Scan on *SELECT* (cost=0.81..17.47 rows=25 width=924) (actual time=411.037..794.024 rows=4,750 loops=1)

14. 380.651 791.443 ↓ 190.0 4,750 1

Hash Right Join (cost=0.81..17.09 rows=25 width=694) (actual time=411.035..791.443 rows=4,750 loops=1)

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

16. 8.847 410.778 ↓ 190.0 4,750 1

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

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

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

18.          

CTE insert_items

19. 79.759 103.241 ↓ 7.5 1,494 1

Insert on traffic_replay_item_apps3034 (cost=0.29..205.12 rows=198 width=524) (actual time=0.476..103.241 rows=1,494 loops=1)

20. 10.170 23.482 ↓ 7.5 1,494 1

Nested Loop (cost=0.29..205.12 rows=198 width=524) (actual time=0.391..23.482 rows=1,494 loops=1)

21. 3.812 3.812 ↓ 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.011..3.812 rows=4,750 loops=1)

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

23. 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))
24. 1,477.044 1,477.044 ↓ 190.0 4,750 1

CTE Scan on insert_entites (cost=0.00..0.50 rows=25 width=0) (actual time=411.227..1,477.044 rows=4,750 loops=1)

Planning time : 44.221 ms
Execution time : 1,590.971 ms