explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gt82

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 10.127 9,604.481 ↓ 33.0 6,605 1

GroupAggregate (cost=61,524.81..61,685.25 rows=200 width=45) (actual time=8,829.604..9,604.481 rows=6,605 loops=1)

  • Group Key: new_history_user_coordinates.user_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 5630
2. 87.176 9,594.354 ↓ 8.3 47,805 1

Group (cost=61,524.81..61,582.47 rows=5,759 width=69) (actual time=8,829.011..9,594.354 rows=47,805 loops=1)

  • Group Key: new_history_user_coordinates.user_id, (to_char(new_history_user_coordinates.created_at, 'DD Mon YYYY'::text))
3. 2,109.234 9,507.178 ↓ 134.8 777,274 1

Sort (cost=61,524.81..61,539.23 rows=5,768 width=69) (actual time=8,829.009..9,507.178 rows=777,274 loops=1)

  • Sort Key: new_history_user_coordinates.user_id, (to_char(new_history_user_coordinates.created_at, 'DD Mon YYYY'::text))
  • Sort Method: external merge Disk: 44824kB
4. 7,085.381 7,397.944 ↓ 134.8 777,274 1

Bitmap Heap Scan on new_history_user_coordinates (cost=627.64..61,164.49 rows=5,768 width=69) (actual time=321.355..7,397.944 rows=777,274 loops=1)

  • Recheck Cond: ('0103000020E61000000100000006000000CBF5B6990ACB4240CA1649BBD1E14B40D788601C5CCE42408FE3874A23E04B404AB3791C06C14240B8AF03E78CDC4B40A9A0A2EA57C04240020CCB9F6FE14B4056B950F9D7C842404CA8E0F082E24B40CBF5B6990ACB4240CA1649BBD1E14B40'::geometry ~ geopoint)
  • Rows Removed by Index Recheck: 11691030
  • Filter: ((created_at > '2019-04-14 00:00:00'::timestamp without time zone) AND _st_contains('0103000020E61000000100000006000000CBF5B6990ACB4240CA1649BBD1E14B40D788601C5CCE42408FE3874A23E04B404AB3791C06C14240B8AF03E78CDC4B40A9A0A2EA57C04240020CCB9F6FE14B4056B950F9D7C842404CA8E0F082E24B40CBF5B6990ACB4240CA1649BBD1E14B40'::geometry, geopoint))
  • Rows Removed by Filter: 289901
  • Heap Blocks: exact=40806 lossy=238071
5. 312.563 312.563 ↓ 61.7 1,067,175 1

Bitmap Index Scan on geopoint_idx (cost=0.00..626.20 rows=17,304 width=0) (actual time=312.563..312.563 rows=1,067,175 loops=1)

  • Index Cond: ('0103000020E61000000100000006000000CBF5B6990ACB4240CA1649BBD1E14B40D788601C5CCE42408FE3874A23E04B404AB3791C06C14240B8AF03E78CDC4B40A9A0A2EA57C04240020CCB9F6FE14B4056B950F9D7C842404CA8E0F082E24B40CBF5B6990ACB4240CA1649BBD1E14B40'::geometry ~ geopoint)
Planning time : 0.640 ms
Execution time : 9,612.169 ms