explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EyyI : Optimization for: Optimization for: Optimization for: plan #VZqr; plan #8SUJ; plan #u5FS

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 66.957 1,762.997 ↑ 1.0 1 1

Aggregate (cost=191,845.12..191,845.13 rows=1 width=32) (actual time=1,762.997..1,762.997 rows=1 loops=1)

2. 5.799 1,696.040 ↓ 19.9 13,355 1

Hash Join (cost=190,981.09..191,672.59 rows=670 width=93) (actual time=1,686.866..1,696.040 rows=13,355 loops=1)

  • Hash Cond: ((stop.durak_no)::text = trips_with_zones_mt."BOARDING")
3. 3.409 3.409 ↑ 1.0 14,438 1

Index Scan using stop_idx on stop (cost=0.29..653.85 rows=14,438 width=53) (actual time=0.022..3.409 rows=14,438 loops=1)

4. 0.603 1,686.832 ↓ 17.8 3,561 1

Hash (cost=190,978.31..190,978.31 rows=200 width=45) (actual time=1,686.832..1,686.832 rows=3,561 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB
5. 118.938 1,686.229 ↓ 17.8 3,561 1

HashAggregate (cost=190,973.81..190,976.31 rows=200 width=45) (actual time=1,685.121..1,686.229 rows=3,561 loops=1)

  • Group Key: trips_with_zones_mt."BOARDING
6. 1,037.847 1,567.291 ↓ 2.2 517,093 1

HashAggregate (cost=184,471.93..186,836.25 rows=236,432 width=48) (actual time=1,402.083..1,567.291 rows=517,093 loops=1)

  • Group Key: trips_with_zones_mt."TRIP_ID", trips_with_zones_mt."BOARDING
7. 352.601 529.444 ↑ 1.0 2,364,324 1

Bitmap Heap Scan on trips_with_zones_mt (cost=88,233.26..166,739.50 rows=2,364,324 width=40) (actual time=190.226..529.444 rows=2,364,324 loops=1)

  • Heap Blocks: exact=54863
8. 176.843 176.843 ↑ 1.0 2,364,324 1

Bitmap Index Scan on boarding_trip_id_idx (cost=0.00..87,642.17 rows=2,364,324 width=0) (actual time=176.843..176.843 rows=2,364,324 loops=1)