explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GQ1G

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 4,777.949 ↑ 1.0 1 1

Aggregate (cost=7,427.83..7,427.84 rows=1 width=0) (actual time=4,777.949..4,777.949 rows=1 loops=1)

2. 0.034 4,777.929 ↓ 17.0 17 1

Nested Loop (cost=347.19..7,427.83 rows=1 width=0) (actual time=94.447..4,777.929 rows=17 loops=1)

3. 0.031 4,777.810 ↓ 17.0 17 1

Nested Loop (cost=346.91..7,426.52 rows=1 width=4) (actual time=94.424..4,777.810 rows=17 loops=1)

4. 298.899 4,777.643 ↓ 17.0 17 1

Nested Loop (cost=346.62..7,424.20 rows=1 width=12) (actual time=94.409..4,777.643 rows=17 loops=1)

  • Join Filter: (a.id = n.address_id)
  • Rows Removed by Join Filter: 3487607
5. 6.412 16.314 ↓ 686.0 686 1

Hash Join (cost=346.62..1,342.48 rows=1 width=16) (actual time=2.524..16.314 rows=686 loops=1)

  • Hash Cond: ((a.street_id = ls.id) AND (upper((a.house)::text) = upper((z.house_number)::text)))
6. 7.460 7.460 ↓ 1.0 3,418 1

Seq Scan on addresses a (cost=0.00..962.04 rows=3,381 width=15) (actual time=0.015..7.460 rows=3,418 loops=1)

  • Filter: (city_id = 1865)
  • Rows Removed by Filter: 38585
7. 0.380 2.442 ↓ 173.0 692 1

Hash (cost=346.56..346.56 rows=4 width=19) (actual time=2.442..2.442 rows=692 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
8. 0.273 2.062 ↓ 173.0 692 1

Hash Join (cost=139.47..346.56 rows=4 width=19) (actual time=1.312..2.062 rows=692 loops=1)

  • Hash Cond: (z.streetid = ls.id)
9. 1.372 1.372 ↑ 1.0 1,728 1

Seq Scan on qgis_zasiegi z (cost=0.00..200.57 rows=1,728 width=11) (actual time=0.879..1.372 rows=1,728 loops=1)

  • Filter: (cityid = 1865)
  • Rows Removed by Filter: 5038
10. 0.044 0.417 ↑ 1.0 202 1

Hash (cost=136.88..136.88 rows=207 width=8) (actual time=0.417..0.417 rows=202 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.373 0.373 ↑ 1.0 202 1

Index Scan using location_streets_cityid_name_ident_key on location_streets ls (cost=0.41..136.88 rows=207 width=8) (actual time=0.039..0.373 rows=202 loops=1)

  • Index Cond: (cityid = 1865)
12. 4,462.430 4,462.430 ↑ 1.0 5,084 686

Seq Scan on nodes n (cost=0.00..6,018.18 rows=5,084 width=4) (actual time=0.013..6.505 rows=5,084 loops=686)

  • Filter: (linktype = 1)
  • Rows Removed by Filter: 8210
13. 0.136 0.136 ↑ 1.0 1 17

Index Scan using location_cities_pkey on location_cities lc (cost=0.29..2.31 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=17)

  • Index Cond: (id = 1865)
14. 0.085 0.085 ↑ 1.0 1 17

Index Only Scan using location_boroughs_pkey on location_boroughs lb (cost=0.28..1.29 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=17)

  • Index Cond: (id = lc.boroughid)
  • Heap Fetches: 0