explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XMgW

Settings
# exclusive inclusive rows x rows loops node
1. 26.972 3,826.296 ↓ 9.8 215,920 1

Limit (cost=16,158.06..16,213.12 rows=22,022 width=295) (actual time=3,697.698..3,826.296 rows=215,920 loops=1)

  • Buffers: shared hit=1025739 dirtied=33, temp read=7683 written=7684
2. 671.340 3,799.324 ↓ 9.8 215,920 1

Sort (cost=16,158.06..16,213.12 rows=22,022 width=295) (actual time=3,697.694..3,799.324 rows=215,920 loops=1)

  • Sort Method: external merge Disk: 61464kB
  • Buffers: shared hit=1025739 dirtied=33, temp read=7683 written=7684
3. 716.958 3,127.984 ↓ 9.8 215,920 1

Nested Loop (cost=2.38..14,569.54 rows=22,022 width=295) (actual time=550.548..3,127.984 rows=215,920 loops=1)

  • Buffers: shared hit=1025739 dirtied=33
4. 150.623 1,331.426 ↓ 9.8 215,920 1

Hash Join (cost=1.95..1,977.13 rows=22,022 width=279) (actual time=550.480..1,331.426 rows=215,920 loops=1)

  • Buffers: shared hit=162059 dirtied=33
5. 70.724 1,180.768 ↓ 9.8 215,920 1

Nested Loop (cost=0.43..1,907.16 rows=22,022 width=260) (actual time=550.402..1,180.768 rows=215,920 loops=1)

  • Buffers: shared hit=162058 dirtied=33
6. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on geoserver_acl g (cost=0.00..1.04 rows=1 width=175) (actual time=0.017..0.020 rows=1 loops=1)

  • Filter: (username = 'cmaoi'::text)
  • Buffers: shared hit=1
7. 37.683 1,110.024 ↓ 1,468.8 215,920 1

Append (cost=0.43..1,904.65 rows=147 width=260) (actual time=550.378..1,110.024 rows=215,920 loops=1)

  • Buffers: shared hit=162057 dirtied=33
8. 0.023 0.023 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_06_11_received_date_time_idx on contact_reports_p2019_06_11 cr (cost=0.43..8.47 rows=1 width=258) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone))
  • Filter: ((geopoint && g.geometry) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=3
9. 897.011 1,072.170 ↓ 1,542.3 215,920 1

Bitmap Heap Scan on contact_reports_p2019_06_12 cr_1 (cost=19.98..1,846.40 rows=140 width=260) (actual time=550.346..1,072.170 rows=215,920 loops=1)

  • Filter: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=162048 dirtied=33
10. 175.159 175.159 ↓ 1,846.3 869,605 1

Bitmap Index Scan on contact_reports_p2019_06_12_geopoint_idx (cost=0.00..19.95 rows=471 width=0) (actual time=175.159..175.159 rows=869,605 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared hit=5592
11. 0.040 0.040 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_06_13_geopoint_idx on contact_reports_p2019_06_13 cr_2 (cost=0.14..8.18 rows=1 width=236) (actual time=0.040..0.040 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
12. 0.020 0.020 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_06_14_geopoint_idx on contact_reports_p2019_06_14 cr_3 (cost=0.14..8.18 rows=1 width=236) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
13. 0.029 0.029 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_06_15_geopoint_idx on contact_reports_p2019_06_15 cr_4 (cost=0.14..8.18 rows=1 width=236) (actual time=0.029..0.029 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
14. 0.020 0.020 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_06_16_geopoint_idx on contact_reports_p2019_06_16 cr_5 (cost=0.14..8.18 rows=1 width=236) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
15. 0.020 0.020 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_06_17_geopoint_idx on contact_reports_p2019_06_17 cr_6 (cost=0.14..8.18 rows=1 width=236) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
16. 0.019 0.019 ↓ 0.0 0 1

Index Scan using contact_reports_default_geopoint_idx on contact_reports_default cr_7 (cost=0.14..8.18 rows=1 width=236) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
17. 0.013 0.035 ↓ 1.1 26 1

Hash (cost=1.23..1.23 rows=23 width=23) (actual time=0.035..0.035 rows=26 loops=1)

  • Buffers: shared hit=1
18. 0.022 0.022 ↓ 1.1 26 1

Seq Scan on providers p (cost=0.00..1.23 rows=23 width=23) (actual time=0.016..0.022 rows=26 loops=1)

  • Buffers: shared hit=1
19. 1,079.600 1,079.600 ↑ 1.0 1 215,920

Index Scan using pk_identities on identities i (cost=0.43..0.55 rows=1 width=204) (actual time=0.005..0.005 rows=1 loops=215,920)

  • Index Cond: (identity_id = cr.identity_id)
  • Buffers: shared hit=863680