explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KAY7

Settings
# exclusive inclusive rows x rows loops node
1. 3.576 457.382 ↑ 12.7 17,285 1

Unique (cost=389,125.51..390,766.63 rows=218,816 width=8) (actual time=452.375..457.382 rows=17,285 loops=1)

2. 7.502 453.806 ↑ 12.7 17,285 1

Sort (cost=389,125.51..389,672.55 rows=218,816 width=8) (actual time=452.374..453.806 rows=17,285 loops=1)

  • Sort Key: rtrs.record_data_id, rst.street_data_id
  • Sort Method: quicksort Memory: 1579kB
3. 20.434 446.304 ↑ 12.7 17,285 1

Hash Join (cost=349,257.60..367,964.49 rows=218,816 width=8) (actual time=285.773..446.304 rows=17,285 loops=1)

  • Hash Cond: (rtrs.record_data_id = r.record_data_id)
  • Join Filter: ((r.is_latest_until IS NULL) OR (r.is_latest_until > (SubPlan 2)))
  • Rows Removed by Join Filter: 2561
4. 21.751 193.115 ↑ 25.2 20,172 1

Hash Join (cost=22,003.08..34,885.46 rows=508,757 width=8) (actual time=71.227..193.115 rows=20,172 loops=1)

  • Hash Cond: (rs.street_library_id = rst.street_library_id)
5. 89.987 167.096 ↓ 1.0 146,377 1

Hash Join (cost=7,632.44..12,140.08 rows=146,188 width=8) (actual time=60.922..167.096 rows=146,377 loops=1)

  • Hash Cond: (rtrs.segment_data_id = rs.segment_data_id)
6. 16.382 16.382 ↓ 1.0 146,377 1

Seq Scan on records_to_record_segments rtrs (cost=0.00..2,212.88 rows=146,188 width=8) (actual time=0.010..16.382 rows=146,377 loops=1)

7. 29.688 60.727 ↑ 1.4 141,834 1

Hash (cost=4,413.53..4,413.53 rows=196,153 width=8) (actual time=60.727..60.727 rows=141,834 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2418kB
8. 31.039 31.039 ↑ 1.4 141,834 1

Seq Scan on record_segments rs (cost=0.00..4,413.53 rows=196,153 width=8) (actual time=0.005..31.039 rows=141,834 loops=1)

9. 0.006 4.268 ↑ 82.4 15 1

Hash (cost=14,355.19..14,355.19 rows=1,236 width=12) (actual time=4.268..4.268 rows=15 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
10. 0.212 4.262 ↑ 82.4 15 1

Hash Join (cost=13,781.90..14,355.19 rows=1,236 width=12) (actual time=3.429..4.262 rows=15 loops=1)

  • Hash Cond: (rst.street_library_id = (unnest((array_agg(sly.street_library_id)))))
11. 0.655 0.655 ↑ 1.7 1,429 1

Seq Scan on record_streets rst (cost=0.00..559.73 rows=2,473 width=8) (actual time=0.008..0.655 rows=1,429 loops=1)

12. 0.003 3.395 ↑ 66.7 3 1

Hash (cost=13,779.40..13,779.40 rows=200 width=4) (actual time=3.395..3.395 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.007 3.392 ↑ 66.7 3 1

HashAggregate (cost=13,777.40..13,779.40 rows=200 width=4) (actual time=3.391..3.392 rows=3 loops=1)

  • Group Key: unnest((array_agg(sly.street_library_id)))
14. 0.006 3.385 ↑ 224,800.0 3 1

ProjectSet (cost=1,655.06..5,347.40 rows=674,400 width=4) (actual time=3.358..3.385 rows=3 loops=1)

15. 0.407 3.379 ↑ 6,744.0 1 1

GroupAggregate (cost=1,655.06..1,857.38 rows=6,744 width=64) (actual time=3.353..3.379 rows=1 loops=1)

  • Group Key: (CASE WHEN (sly.street_name IS NOT NULL) THEN (sly.street_name)::text ELSE (sly.osm_ids[1])::text END)
  • Filter: (182 = ANY (array_agg(sly.street_library_id)))
  • Rows Removed by Filter: 624
16. 1.247 2.972 ↑ 8.5 793 1

Sort (cost=1,655.06..1,671.92 rows=6,744 width=36) (actual time=2.916..2.972 rows=793 loops=1)

  • Sort Key: (CASE WHEN (sly.street_name IS NOT NULL) THEN (sly.street_name)::text ELSE (sly.osm_ids[1])::text END)
  • Sort Method: quicksort Memory: 76kB
17. 1.725 1.725 ↑ 8.5 793 1

Seq Scan on streets_library sly (cost=0.00..1,226.16 rows=6,744 width=36) (actual time=0.007..1.725 rows=793 loops=1)

18. 37.752 214.375 ↓ 1.4 144,142 1

Hash (cost=325,447.60..325,447.60 rows=103,913 width=12) (actual time=214.375..214.375 rows=144,142 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3748kB
19. 30.246 176.623 ↓ 1.4 144,142 1

Seq Scan on records r (cost=0.00..325,447.60 rows=103,913 width=12) (actual time=0.017..176.623 rows=144,142 loops=1)

  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 2235
20.          

SubPlan (for Seq Scan)

21. 146.377 146.377 ↓ 0.0 0 146,377

Index Scan using idx_records_created_at on records (cost=0.42..2.64 rows=1 width=4) (actual time=0.000..0.001 rows=0 loops=146,377)

  • Index Cond: (created_at = r.is_latest_until)
22.          

SubPlan (for Hash Join)

23. 18.380 18.380 ↑ 1.0 1 9,190

Index Scan using record_streets_pkey on record_streets (cost=0.28..2.50 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=9,190)

  • Index Cond: (street_data_id = rst.street_data_id)
Planning time : 18.496 ms
Execution time : 458.510 ms