explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1aZc

Settings
# exclusive inclusive rows x rows loops node
1. 24.048 49,196.448 ↑ 2.1 84,332 1

Unique (cost=1,371,874.87..1,376,319.77 rows=173,605 width=783) (actual time=49,102.679..49,196.448 rows=84,332 loops=1)

2.          

CTE day_tz

3. 0.188 0.195 ↑ 3.4 8 1

Index Scan using day_of_operation_date on day_of_operation doo (cost=0.37..8.91 rows=27 width=4) (actual time=0.179..0.195 rows=8 loops=1)

  • Index Cond: ((day >= (timezone(($0)::text, '2020-06-30 07:04:57+00'::timestamp with time zone))::date) AND (day <= (timezone(($1)::text, '2020-07-07 07:04:57+00'::timestamp with time zone))::date))
4.          

Initplan (for Index Scan)

5. 0.001 0.005 ↑ 1.0 1 1

Limit (cost=0.00..0.04 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=1)

6. 0.004 0.004 ↑ 640.0 1 1

Seq Scan on agency (cost=0.00..24.40 rows=640 width=17) (actual time=0.004..0.004 rows=1 loops=1)

7. 0.000 0.002 ↑ 1.0 1 1

Limit (cost=0.00..0.04 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=1)

8. 0.002 0.002 ↑ 640.0 1 1

Seq Scan on agency agency_1 (cost=0.00..24.40 rows=640 width=17) (actual time=0.002..0.002 rows=1 loops=1)

9. 292.028 49,172.400 ↑ 3.2 279,625 1

Sort (cost=1,371,865.96..1,374,088.41 rows=888,980 width=783) (actual time=49,102.677..49,172.400 rows=279,625 loops=1)

  • Sort Key: til.trip_id, til.day_of_operation_id DESC
  • Sort Method: external merge Disk: 56,264kB
10. 43.763 48,880.372 ↑ 3.2 279,625 1

Nested Loop (cost=0.81..670,245.77 rows=888,980 width=783) (actual time=2.719..48,880.372 rows=279,625 loops=1)

11. 0.209 0.209 ↑ 3.4 8 1

CTE Scan on day_tz dt (cost=0.00..0.54 rows=27 width=4) (actual time=0.180..0.209 rows=8 loops=1)

12. 48,836.400 48,836.400 ↓ 1.1 34,953 8

Index Only Scan using trip_instance_log_doo_actuals on trip_instance_log til (cost=0.81..24,494.65 rows=32,925 width=783) (actual time=0.932..6,104.550 rows=34,953 loops=8)

  • Index Cond: (day_of_operation_id = dt.day_of_operation_id)
  • Heap Fetches: 283,508
Planning time : 1.790 ms
Execution time : 49,210.355 ms