explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iLH4

Settings
# exclusive inclusive rows x rows loops node
1. 12.478 1,265.175 ↑ 1.0 20 1

Limit (cost=50,916.37..50,916.42 rows=20 width=42) (actual time=1,252.696..1,265.175 rows=20 loops=1)

2. 0.248 1,252.697 ↑ 11.2 20 1

Sort (cost=50,916.37..50,916.94 rows=225 width=42) (actual time=1,252.695..1,252.697 rows=20 loops=1)

  • Sort Key: (count(vt.transittime)) DESC
  • Sort Method: top-N heapsort Memory: 30kB
3. 0.000 1,252.449 ↑ 1.1 196 1

Finalize GroupAggregate (cost=50,837.81..50,910.39 rows=225 width=42) (actual time=1,234.317..1,252.449 rows=196 loops=1)

  • Group Key: d1.name, d2.name
4. 299.953 1,263.445 ↑ 1.2 384 1

Gather Merge (cost=50,837.81..50,902.51 rows=450 width=58) (actual time=1,234.253..1,263.445 rows=384 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
5. 13.718 963.492 ↑ 1.2 192 2 / 2

Partial GroupAggregate (cost=49,837.78..49,850.55 rows=225 width=58) (actual time=948.192..963.492 rows=192 loops=2)

  • Group Key: d1.name, d2.name
6. 65.791 949.774 ↓ 20.7 17,426 2 / 2

Sort (cost=49,837.78..49,839.89 rows=841 width=34) (actual time=948.151..949.774 rows=17,426 loops=2)

  • Sort Key: d1.name, d2.name
  • Sort Method: quicksort Memory: 3,150kB
7. 11.982 883.983 ↓ 20.7 17,426 2 / 2

Hash Join (cost=16.09..49,796.93 rows=841 width=34) (actual time=10.910..883.983 rows=17,426 loops=2)

  • Hash Cond: (vt.destdeviceid = (d2.deviceid)::text)
8. 37.543 871.592 ↓ 1.9 17,763 2 / 2

Hash Join (cost=8.05..49,733.08 rows=9,478 width=30) (actual time=10.454..871.592 rows=17,763 loops=2)

  • Hash Cond: (vt.origindeviceid = (d1.deviceid)::text)
9. 833.877 833.877 ↓ 1.2 130,180 2 / 2

Parallel Seq Scan on visitortransits vt (cost=0.00..49,096.36 rows=106,780 width=26) (actual time=0.715..833.877 rows=130,180 loops=2)

  • Filter: ((origintime >= '2020-01-31 15:33:59.685+10:30'::timestamp with time zone) AND (origintime <= '2020-02-07 15:33:59.686+10:30'::timestamp with time zone))
  • Rows Removed by Filter: 499,369
10. 0.034 0.172 ↑ 1.0 15 2 / 2

Hash (cost=7.86..7.86 rows=15 width=17) (actual time=0.172..0.172 rows=15 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.138 0.138 ↑ 1.0 15 2 / 2

Seq Scan on devices d1 (cost=0.00..7.86 rows=15 width=17) (actual time=0.028..0.138 rows=15 loops=2)

  • Filter: ((name)::text = ANY ('{"Corner of McLaren Flat and Elliot Roads",d''Arenberg,"Chapel Hill Winery Tasting Room and Gallery","Maxwell Wines","Yangarra Estate Vineyard","Woodstock Wine Estate","Visitor Information Centre","Wirra Wirra Vineyards","The Salopian Inn","Willunga Farmers Market","Oxenberry Farm Wines","Kay Brothers","Olivers Taranga",Shingleback,"Star of Greece"}'::text[]))
  • Rows Removed by Filter: 154
12. 0.033 0.409 ↑ 1.0 15 2 / 2

Hash (cost=7.86..7.86 rows=15 width=17) (actual time=0.409..0.409 rows=15 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.376 0.376 ↑ 1.0 15 2 / 2

Seq Scan on devices d2 (cost=0.00..7.86 rows=15 width=17) (actual time=0.307..0.376 rows=15 loops=2)

  • Filter: ((name)::text = ANY ('{"Corner of McLaren Flat and Elliot Roads",d''Arenberg,"Chapel Hill Winery Tasting Room and Gallery","Maxwell Wines","Yangarra Estate Vineyard","Woodstock Wine Estate","Visitor Information Centre","Wirra Wirra Vineyards","The Salopian Inn","Willunga Farmers Market","Oxenberry Farm Wines","Kay Brothers","Olivers Taranga",Shingleback,"Star of Greece"}'::text[]))
  • Rows Removed by Filter: 154
Planning time : 46.219 ms
Execution time : 1,266.156 ms