explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k1Xr

Settings
# exclusive inclusive rows x rows loops node
1. 10.439 70,836.677 ↓ 32.6 6,517 1

GroupAggregate (cost=5,648,104.99..5,648,207.45 rows=200 width=45) (actual time=69,929.089..70,836.677 rows=6,517 loops=1)

  • Group Key: new_history_user_coordinates.user_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 5578
2. 106.904 70,826.238 ↓ 11.7 46,865 1

Group (cost=5,648,104.99..5,648,135.14 rows=4,018 width=41) (actual time=69,928.687..70,826.238 rows=46,865 loops=1)

  • Group Key: new_history_user_coordinates.user_id, new_history_user_coordinates.date
3. 2,414.385 70,719.334 ↓ 191.2 768,802 1

Sort (cost=5,648,104.99..5,648,115.04 rows=4,020 width=41) (actual time=69,928.685..70,719.334 rows=768,802 loops=1)

  • Sort Key: new_history_user_coordinates.user_id, new_history_user_coordinates.date
  • Sort Method: external merge Disk: 40536kB
4. 17,375.812 68,304.949 ↓ 191.2 768,802 1

Bitmap Heap Scan on new_history_user_coordinates (cost=1,769,857.38..5,647,864.33 rows=4,020 width=41) (actual time=51,015.920..68,304.949 rows=768,802 loops=1)

  • Recheck Cond: (date > '2019-04-14'::date)
  • Rows Removed by Index Recheck: 5656
  • Filter: (('0103000020E61000000100000006000000CBF5B6990ACB4240CA1649BBD1E14B40D788601C5CCE42408FE3874A23E04B404AB3791C06C14240B8AF03E78CDC4B40A9A0A2EA57C04240020CCB9F6FE14B4056B950F9D7C842404CA8E0F082E24B40CBF5B6990ACB4240CA1649BBD1E14B40'::geometry ~ geopoint) AND _st_contains('0103000020E61000000100000006000000CBF5B6990ACB4240CA1649BBD1E14B40D788601C5CCE42408FE3874A23E04B404AB3791C06C14240B8AF03E78CDC4B40A9A0A2EA57C04240020CCB9F6FE14B4056B950F9D7C842404CA8E0F082E24B40CBF5B6990ACB4240CA1649BBD1E14B40'::geometry, geopoint))
  • Rows Removed by Filter: 16149280
  • Heap Blocks: exact=25161 lossy=344507
5. 50,929.137 50,929.137 ↓ 1.4 17,196,661 1

Bitmap Index Scan on user_id_created_at_idx (cost=0.00..1,769,856.37 rows=12,059,547 width=0) (actual time=50,929.137..50,929.137 rows=17,196,661 loops=1)

  • Index Cond: (date > '2019-04-14'::date)
Planning time : 0.339 ms
Execution time : 70,844.939 ms