explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hom5

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 9,187.289 ↓ 0.0 0 1

Unique (cost=828,913.23..828,943.56 rows=3,010 width=734) (actual time=9,187.289..9,187.289 rows=0 loops=1)

  • JIT:
  • Functions: 152
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 60.406 ms, Inlining 29.739 ms, Optimization 5522.724 ms, Emission 3567.442 ms, Total 9180.310 ms
2. 0.011 9,187.288 ↓ 0.0 0 1

Sort (cost=828,913.23..828,920.81 rows=3,033 width=734) (actual time=9,187.288..9,187.288 rows=0 loops=1)

  • Sort Key: tt.start, tt.stop, tt.type
  • Sort Method: quicksort Memory: 25kB
3. 0.004 9,187.277 ↓ 0.0 0 1

Subquery Scan on tt (cost=624,942.17..828,737.82 rows=3,033 width=734) (actual time=9,187.277..9,187.277 rows=0 loops=1)

  • Filter: (((tt.type)::integer = 2) AND "overlaps"(timezone('UTC'::text, tt.start), timezone('UTC'::text, tt.stop), '2020-06-26 22:00:00'::timestamp without time zone, '2020-06-27 21:59:59'::timestam
  • Rows Removed by Filter: 2
4. 0.072 9,187.273 ↑ 909,802.0 2 1

WindowAgg (cost=624,942.17..804,173.17 rows=1,819,604 width=742) (actual time=9,187.255..9,187.273 rows=2 loops=1)

5. 0.013 9,187.201 ↑ 909,802.0 2 1

Sort (cost=624,942.17..629,491.18 rows=1,819,604 width=640) (actual time=9,187.201..9,187.201 rows=2 loops=1)

  • Sort Key: (timezone('UTC'::text, "*SELECT* 1".start))
  • Sort Method: quicksort Memory: 27kB
6. 0.152 9,187.188 ↑ 909,802.0 2 1

Hash Left Join (cost=13,060.19..221,889.49 rows=1,819,604 width=640) (actual time=9,186.902..9,187.188 rows=2 loops=1)

  • Hash Cond: (pv.personid = p.personid)
7. 0.034 9,159.987 ↑ 909,802.0 2 1

Nested Loop Left Join (cost=12,056.28..211,560.05 rows=1,819,604 width=618) (actual time=9,159.704..9,159.987 rows=2 loops=1)

8. 0.285 9,159.713 ↑ 7,856.5 2 1

HashAggregate (cost=12,051.07..12,066.78 rows=15,713 width=726) (actual time=9,159.545..9,159.713 rows=2 loops=1)

  • Group Key: (223076), "*SELECT* 1".unitid, "*SELECT* 1".type, "*SELECT* 1".moment_type_name, "*SELECT* 1".start, "*SELECT* 1".stop, "*SELECT* 1".start_latitude, "*SELEC
9. 0.006 9,159.428 ↑ 7,856.5 2 1

Append (cost=0.41..9,379.86 rows=15,713 width=726) (actual time=9,124.728..9,159.428 rows=2 loops=1)

10. 0.007 9,124.728 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.41..0.70 rows=1 width=726) (actual time=9,124.727..9,124.728 rows=1 loops=1)

11. 9,124.721 9,124.721 ↑ 1.0 1 1

Limit (cost=0.41..0.69 rows=1 width=734) (actual time=9,124.721..9,124.721 rows=1 loops=1)

  • -> Index Scan Backward using idx_moment_87071_start_stop_utc on moment_87071 (cost=0.41..13192.78 rows=47,135 width=734) (actual time=0.068..0.068 r
  • Index Cond: ((timezone('UTC'::text, start) < '2020-06-26 22:00:00'::timestamp without time zone) AND (timezone('UTC'::text, stop) < '2020-06-26
12. 34.694 34.694 ↑ 15,712.0 1 1

Seq Scan on moment_87071 moment_87071_1 (cost=0.00..9,355.60 rows=15,712 width=726) (actual time=12.550..34.694 rows=1 loops=1)

  • Filter: "overlaps"(timezone('UTC'::text, start), timezone('UTC'::text, stop), '2020-06-26 22:00:00'::timestamp without time zone, '2020-06-27 21:59:59'::ti
  • Rows Removed by Filter: 47,136
13. 0.008 0.240 ↓ 0.0 0 2

Bitmap Heap Scan on pvhistory pv (cost=5.21..12.58 rows=116 width=20) (actual time=0.120..0.120 rows=0 loops=2)

  • Recheck Cond: (((vehicleid = 223,076) AND (timezone('UTC'::text, "*SELECT* 1".start) >= timezone('UTC'::text, startdate)) AND (timezone('UTC'::text, "*SELECT* 1".start)
  • Filter: isdriver
14. 0.028 0.232 ↓ 0.0 0 2

BitmapOr (cost=5.21..5.21 rows=119 width=0) (actual time=0.116..0.116 rows=0 loops=2)

15. 0.196 0.196 ↓ 0.0 0 2

Bitmap Index Scan on idx_pvhistory_vehicleid_startdate_enddate (cost=0.00..3.63 rows=30 width=0) (actual time=0.098..0.098 rows=0 loops=2)

  • Index Cond: ((vehicleid = 223,076) AND (timezone('UTC'::text, startdate) <= timezone('UTC'::text, "*SELECT* 1".start)) AND (timezone('UTC'::text, enddate) >
16. 0.008 0.008 ↓ 0.0 0 2

Bitmap Index Scan on idx_pvhistory_vehicleid_startdate_enddate (cost=0.00..1.52 rows=89 width=0) (actual time=0.004..0.004 rows=0 loops=2)

  • Index Cond: ((vehicleid = 223,076) AND (timezone('UTC'::text, startdate) >= timezone('UTC'::text, "*SELECT* 1".start)) AND (timezone('UTC'::text, startdate)
17. 15.383 27.049 ↑ 1.0 56,531 1

Hash (cost=804.56..804.56 rows=56,957 width=18) (actual time=27.049..27.049 rows=56,531 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,403kB
18. 11.666 11.666 ↑ 1.0 56,531 1

Seq Scan on person p (cost=0.00..804.56 rows=56,957 width=18) (actual time=0.017..11.666 rows=56,531 loops=1)

Planning time : 4.714 ms
Execution time : 9,249.124 ms