explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8QXy

Settings
# exclusive inclusive rows x rows loops node
1. 0.528 4,607.562 ↓ 10.0 10 1

Subquery Scan on r (cost=22,375.32..22,375.36 rows=1 width=32) (actual time=4,607.085..4,607.562 rows=10 loops=1)

  • Buffers: shared hit=605407 read=382043 dirtied=3
2. 0.106 4,607.034 ↓ 10.0 10 1

Sort (cost=22,375.32..22,375.32 rows=1 width=136) (actual time=4,607.012..4,607.034 rows=10 loops=1)

  • Sort Key: s.shift_day DESC, t.sequence_num
  • Sort Method: quicksort Memory: 105kB
  • Buffers: shared hit=605407 read=382043 dirtied=3
3. 0.897 4,606.928 ↓ 10.0 10 1

Nested Loop Left Join (cost=746.74..22,375.31 rows=1 width=136) (actual time=2,259.571..4,606.928 rows=10 loops=1)

  • Buffers: shared hit=605407 read=382043 dirtied=3
4. 0.043 4,604.761 ↓ 10.0 10 1

Nested Loop Left Join (cost=744.34..22,360.77 rows=1 width=816) (actual time=2,259.203..4,604.761 rows=10 loops=1)

  • Buffers: shared hit=605198 read=382032 dirtied=3
5. 322.530 4,601.958 ↓ 10.0 10 1

Hash Join (cost=741.94..22,346.26 rows=1 width=792) (actual time=2,258.094..4,601.958 rows=10 loops=1)

  • Hash Cond: (t.shift = s.id)
  • Buffers: shared hit=605078 read=381982 dirtied=3
6. 4,247.791 4,247.791 ↓ 7.5 693,427 1

Index Scan using trip_trip_day_idx on trip t (cost=0.57..21,256.41 rows=92,921 width=672) (actual time=0.035..4,247.791 rows=693,427 loops=1)

  • Index Cond: (trip_day = '2019-07-23'::date)
  • Buffers: shared hit=604992 read=381854 dirtied=1
7. 0.259 31.637 ↓ 1.5 218 1

Hash (cost=736.75..736.75 rows=142 width=136) (actual time=31.636..31.637 rows=218 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
  • Buffers: shared hit=83 read=128 dirtied=2
8. 0.186 31.378 ↓ 1.5 218 1

Nested Loop (cost=0.99..736.75 rows=142 width=136) (actual time=0.478..31.378 rows=218 loops=1)

  • Buffers: shared hit=83 read=128 dirtied=2
9. 0.029 0.029 ↑ 1.0 1 1

Index Scan using person_username_idx on person p (cost=0.42..4.49 rows=1 width=8) (actual time=0.027..0.029 rows=1 loops=1)

  • Index Cond: ((username)::text = '804938'::text)
  • Buffers: shared hit=2 read=2
10. 31.163 31.163 ↑ 3.6 218 1

Index Scan using person_shift_person_idx on person_shift s (cost=0.57..708.90 rows=779 width=144) (actual time=0.449..31.163 rows=218 loops=1)

  • Index Cond: (person = p.id)
  • Buffers: shared hit=81 read=126 dirtied=2
11. 0.640 2.760 ↑ 1.0 1 10

Nested Loop Left Join (cost=2.40..14.48 rows=1 width=40) (actual time=0.270..0.276 rows=1 loops=10)

  • Buffers: shared hit=120 read=50
12. 0.030 2.080 ↑ 1.0 1 10

Nested Loop Left Join (cost=2.26..13.24 rows=1 width=932) (actual time=0.204..0.208 rows=1 loops=10)

  • Buffers: shared hit=100 read=50
13. 0.040 2.040 ↑ 1.0 1 10

Nested Loop (cost=1.70..10.36 rows=1 width=664) (actual time=0.200..0.204 rows=1 loops=10)

  • Buffers: shared hit=100 read=50
14. 0.050 1.840 ↑ 1.0 1 10

Nested Loop (cost=1.14..7.46 rows=1 width=396) (actual time=0.182..0.184 rows=1 loops=10)

  • Buffers: shared hit=60 read=40
15. 1.590 1.590 ↑ 1.0 1 10

Index Scan using "location_markPK" on location_mark lm (cost=0.57..4.57 rows=1 width=172) (actual time=0.158..0.159 rows=1 loops=10)

  • Index Cond: (id = t.from_location)
  • Buffers: shared hit=24 read=26
16. 0.200 0.200 ↑ 1.0 1 10

Index Scan using "person_locatiPK" on person_location pl (cost=0.56..2.87 rows=1 width=240) (actual time=0.019..0.020 rows=1 loops=10)

  • Index Cond: (id = lm.stated_location)
  • Buffers: shared hit=36 read=14
17. 0.160 0.160 ↑ 1.0 1 10

Index Scan using "locationPK" on location plloc (cost=0.56..2.86 rows=1 width=284) (actual time=0.015..0.016 rows=1 loops=10)

  • Index Cond: (id = pl.location)
  • Buffers: shared hit=40 read=10
18. 0.010 0.010 ↓ 0.0 0 10

Index Scan using "locationPK" on location loc (cost=0.56..2.86 rows=1 width=284) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (lm.detected_location = id)
19. 0.040 0.040 ↑ 1.0 1 10

Index Scan using data_entry_source_pkey on data_entry_source des (cost=0.13..1.17 rows=1 width=599) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: (lm.data_entry_source = id)
  • Buffers: shared hit=20
20. 0.710 1.270 ↑ 1.0 1 10

Nested Loop Left Join (cost=2.40..14.48 rows=1 width=40) (actual time=0.119..0.127 rows=1 loops=10)

  • Buffers: shared hit=209 read=11
21. 0.040 0.530 ↑ 1.0 1 10

Nested Loop Left Join (cost=2.26..13.24 rows=1 width=932) (actual time=0.048..0.053 rows=1 loops=10)

  • Buffers: shared hit=189 read=11
22. 0.040 0.440 ↑ 1.0 1 10

Nested Loop (cost=1.70..10.36 rows=1 width=664) (actual time=0.041..0.044 rows=1 loops=10)

  • Buffers: shared hit=139 read=11
23. 0.060 0.290 ↑ 1.0 1 10

Nested Loop (cost=1.14..7.46 rows=1 width=396) (actual time=0.027..0.029 rows=1 loops=10)

  • Buffers: shared hit=94 read=6
24. 0.070 0.070 ↑ 1.0 1 10

Index Scan using "location_markPK" on location_mark lm_1 (cost=0.57..4.57 rows=1 width=172) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (id = t.to_location)
  • Buffers: shared hit=49 read=1
25. 0.160 0.160 ↑ 1.0 1 10

Index Scan using "person_locatiPK" on person_location pl_1 (cost=0.56..2.87 rows=1 width=240) (actual time=0.016..0.016 rows=1 loops=10)

  • Index Cond: (id = lm_1.stated_location)
  • Buffers: shared hit=45 read=5
26. 0.110 0.110 ↑ 1.0 1 10

Index Scan using "locationPK" on location plloc_1 (cost=0.56..2.86 rows=1 width=284) (actual time=0.011..0.011 rows=1 loops=10)

  • Index Cond: (id = pl_1.location)
  • Buffers: shared hit=45 read=5
27. 0.050 0.050 ↑ 1.0 1 10

Index Scan using "locationPK" on location loc_1 (cost=0.56..2.86 rows=1 width=284) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (lm_1.detected_location = id)
  • Buffers: shared hit=50
28. 0.030 0.030 ↑ 1.0 1 10

Index Scan using data_entry_source_pkey on data_entry_source des_1 (cost=0.13..1.17 rows=1 width=599) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: (lm_1.data_entry_source = id)
  • Buffers: shared hit=20
Planning time : 3.097 ms
Execution time : 4,607.753 ms