explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qpz

Settings
# exclusive inclusive rows x rows loops node
1. 11.300 11,060.881 ↓ 33.0 6,605 1

GroupAggregate (cost=61,812.82..61,973.26 rows=200 width=45) (actual time=10,029.533..11,060.881 rows=6,605 loops=1)

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

Group (cost=61,812.82..61,870.48 rows=5,759 width=69) (actual time=10,028.768..11,049.581 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,731.585 10,902.750 ↓ 134.8 777,274 1

Sort (cost=61,812.82..61,827.24 rows=5,768 width=69) (actual time=10,028.765..10,902.750 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,780.851 8,171.165 ↓ 134.8 777,274 1

Bitmap Heap Scan on new_history_user_coordinates (cost=918.89..61,452.50 rows=5,768 width=69) (actual time=401.781..8,171.165 rows=777,274 loops=1)

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

Bitmap Index Scan on geopoint_idx (cost=0.00..917.45 rows=17,303 width=0) (actual time=390.314..390.314 rows=1,067,175 loops=1)

  • Index Cond: (('0103000020E61000000100000006000000CBF5B6990ACB4240CA1649BBD1E14B40D788601C5CCE42408FE3874A23E04B404AB3791C06C14240B8AF03E78CDC4B40A9A0A2EA57C04240020CCB9F6FE14B4056B950F9D7C842404CA8E0F082E24B40CBF5B6990ACB4240CA1649BBD1E14B40'::geometry ~ geopoint) AND (created_at > '2019-04-14 00:00:00'::timestamp without time zone))
Planning time : 0.432 ms
Execution time : 11,070.632 ms