explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dAeA : Optimization for: Optimization for: plan #TcbA; plan #qOFP

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 338.346 2,503.881 ↓ 126.2 82,022 1

Nested Loop (cost=31.75..11,097.77 rows=650 width=277) (actual time=873.743..2,503.881 rows=82,022 loops=1)

  • Buffers: shared hit=586563
2. 147.245 1,755.425 ↓ 126.2 82,022 1

Nested Loop (cost=31.32..10,776.48 rows=650 width=182) (actual time=873.666..1,755.425 rows=82,022 loops=1)

  • Buffers: shared hit=257276
3. 24.312 1,526.158 ↓ 126.2 82,022 1

Nested Loop (cost=31.32..10,695.40 rows=650 width=169) (actual time=873.615..1,526.158 rows=82,022 loops=1)

  • Buffers: shared hit=257275
4. 0.017 0.017 ↑ 3.0 1 1

Seq Scan on geoserver_acl g (cost=0.00..16.88 rows=3 width=64) (actual time=0.011..0.017 rows=1 loops=1)

  • Filter: (username = 'cmaoi'::text)
  • Buffers: shared hit=1
5. 17.173 1,501.829 ↓ 253.2 82,022 1

Append (cost=31.32..3,556.27 rows=324 width=169) (actual time=873.597..1,501.829 rows=82,022 loops=1)

  • Buffers: shared hit=257274
6. 1,184.976 1,484.446 ↓ 257.9 82,022 1

Bitmap Heap Scan on contact_reports_p2019_04_10 cr (cost=31.32..3,521.61 rows=318 width=169) (actual time=873.592..1,484.446 rows=82,022 loops=1)

  • Filter: ((report_date_time > '2019-04-10 08:00:00'::timestamp without time zone) AND ((provider_id = ANY (g.providers)) OR (g.providers IS NULL)))
  • Buffers: shared hit=257268
7. 299.470 299.470 ↓ 1,467.6 1,335,540 1

Bitmap Index Scan on contact_reports_p2019_04_10_geopoint_idx (cost=0.00..31.24 rows=910 width=0) (actual time=299.470..299.470 rows=1,335,540 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared hit=8260
8. 0.049 0.049 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_04_11_geopoint_idx on contact_reports_p2019_04_11 cr_1 (cost=0.14..5.51 rows=1 width=220) (actual time=0.049..0.049 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-04-10 08:00:00'::timestamp without time zone) AND ((provider_id = ANY (g.providers)) OR (g.providers IS NULL)))
  • Buffers: shared hit=1
9. 0.032 0.032 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_04_12_geopoint_idx on contact_reports_p2019_04_12 cr_2 (cost=0.14..5.51 rows=1 width=220) (actual time=0.032..0.032 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-04-10 08:00:00'::timestamp without time zone) AND ((provider_id = ANY (g.providers)) OR (g.providers IS NULL)))
  • Buffers: shared hit=1
10. 0.028 0.028 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_04_13_geopoint_idx on contact_reports_p2019_04_13 cr_3 (cost=0.14..5.51 rows=1 width=220) (actual time=0.028..0.028 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-04-10 08:00:00'::timestamp without time zone) AND ((provider_id = ANY (g.providers)) OR (g.providers IS NULL)))
  • Buffers: shared hit=1
11. 0.041 0.041 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_04_14_geopoint_idx on contact_reports_p2019_04_14 cr_4 (cost=0.14..5.51 rows=1 width=220) (actual time=0.040..0.041 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-04-10 08:00:00'::timestamp without time zone) AND ((provider_id = ANY (g.providers)) OR (g.providers IS NULL)))
  • Buffers: shared hit=1
12. 0.029 0.029 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_04_15_geopoint_idx on contact_reports_p2019_04_15 cr_5 (cost=0.14..5.51 rows=1 width=220) (actual time=0.029..0.029 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-04-10 08:00:00'::timestamp without time zone) AND ((provider_id = ANY (g.providers)) OR (g.providers IS NULL)))
  • Buffers: shared hit=1
13. 0.031 0.031 ↓ 0.0 0 1

Index Scan using contact_reports_default_geopoint_idx on contact_reports_default cr_6 (cost=0.14..5.51 rows=1 width=220) (actual time=0.031..0.031 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-04-10 08:00:00'::timestamp without time zone) AND ((provider_id = ANY (g.providers)) OR (g.providers IS NULL)))
  • Buffers: shared hit=1
14. 82.003 82.022 ↓ 1.1 10 82,022

Materialize (cost=0.00..1.14 rows=9 width=17) (actual time=0.000..0.001 rows=10 loops=82,022)

  • Buffers: shared hit=1
15. 0.019 0.019 ↓ 1.1 10 1

Seq Scan on providers p (cost=0.00..1.09 rows=9 width=17) (actual time=0.017..0.019 rows=10 loops=1)

  • Buffers: shared hit=1
16. 410.110 410.110 ↑ 1.0 1 82,022

Index Scan using pk_identities on identities i (cost=0.42..0.47 rows=1 width=246) (actual time=0.005..0.005 rows=1 loops=82,022)

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