explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sm4q : Optimization for: Optimization for: plan #vXXV; plan #maEZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 27.053 3,939.244 ↓ 9.9 215,111 1

Limit (cost=15,988.28..16,042.56 rows=21,712 width=295) (actual time=3,835.869..3,939.244 rows=215,111 loops=1)

  • Buffers: shared hit=1022514, temp read=7653 written=7654
2. 602.292 3,912.191 ↓ 9.9 215,111 1

Sort (cost=15,988.28..16,042.56 rows=21,712 width=295) (actual time=3,835.867..3,912.191 rows=215,111 loops=1)

  • Sort Method: external merge Disk: 61224kB
  • Buffers: shared hit=1022514, temp read=7653 written=7654
3. 721.582 3,309.899 ↓ 9.9 215,111 1

Nested Loop (cost=2.38..14,424.34 rows=21,712 width=295) (actual time=548.070..3,309.899 rows=215,111 loops=1)

  • Buffers: shared hit=1022514
4. 147.346 1,297.651 ↓ 9.9 215,111 1

Hash Join (cost=1.95..1,976.09 rows=21,712 width=279) (actual time=547.991..1,297.651 rows=215,111 loops=1)

  • Buffers: shared hit=161813
5. 70.664 1,150.249 ↓ 9.9 215,111 1

Nested Loop (cost=0.43..1,907.08 rows=21,712 width=260) (actual time=547.856..1,150.249 rows=215,111 loops=1)

  • Buffers: shared hit=161812
6. 0.018 0.018 ↑ 1.0 1 1

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

  • Filter: (username = 'cmaoi'::text)
  • Buffers: shared hit=1
7. 38.055 1,079.567 ↓ 1,483.5 215,111 1

Append (cost=0.43..1,904.59 rows=145 width=260) (actual time=547.832..1,079.567 rows=215,111 loops=1)

  • Buffers: shared hit=161811
8. 0.017 0.017 ↓ 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.017..0.017 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. 869.124 1,041.259 ↓ 1,558.8 215,111 1

Bitmap Heap Scan on contact_reports_p2019_06_12 cr_1 (cost=19.98..1,846.34 rows=138 width=260) (actual time=547.805..1,041.259 rows=215,111 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=161802
10. 172.135 172.135 ↓ 1,843.5 868,308 1

Bitmap Index Scan on contact_reports_p2019_06_12_geopoint_idx (cost=0.00..19.95 rows=471 width=0) (actual time=172.135..172.135 rows=868,308 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared hit=5585
11. 0.080 0.080 ↓ 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.080..0.080 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.034 0.034 ↓ 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.034..0.034 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.028 0.028 ↓ 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.028..0.028 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.032 0.032 ↓ 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.031..0.032 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.031 0.031 ↓ 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.031..0.031 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.031 0.031 ↓ 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.031..0.031 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.031 0.056 ↓ 1.1 26 1

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

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

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

  • Buffers: shared hit=1
19. 1,290.666 1,290.666 ↑ 1.0 1 215,111

Index Scan using pk_identities on identities i (cost=0.43..0.55 rows=1 width=202) (actual time=0.006..0.006 rows=1 loops=215,111)

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