explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qOFP : Optimization for: plan #TcbA

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 284.773 2,411.696 ↓ 138.0 81,696 1

Nested Loop (cost=31.73..11,038.81 rows=592 width=277) (actual time=869.832..2,411.696 rows=81,696 loops=1)

  • Buffers: shared hit=583548
2. 139.905 1,718.443 ↓ 138.0 81,696 1

Nested Loop (cost=31.31..10,746.16 rows=592 width=182) (actual time=869.738..1,718.443 rows=81,696 loops=1)

  • Buffers: shared hit=256764
3. 23.406 1,496.842 ↓ 138.0 81,696 1

Nested Loop (cost=31.31..10,672.26 rows=592 width=169) (actual time=869.680..1,496.842 rows=81,696 loops=1)

  • Buffers: shared hit=256763
4. 0.027 0.027 ↑ 3.0 1 1

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

  • Filter: (username = 'cmaoi'::text)
  • Buffers: shared hit=1
5. 17.029 1,473.409 ↓ 252.9 81,696 1

Append (cost=31.31..3,548.56 rows=323 width=169) (actual time=869.647..1,473.409 rows=81,696 loops=1)

  • Buffers: shared hit=256762
6. 1,148.440 1,456.153 ↓ 257.7 81,696 1

Bitmap Heap Scan on contact_reports_p2019_04_10 cr (cost=31.31..3,513.91 rows=317 width=169) (actual time=869.641..1,456.153 rows=81,696 loops=1)

  • Filter: ((report_date_time > '2019-04-10 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=256756
7. 307.713 307.713 ↓ 1,467.1 1,332,172 1

Bitmap Index Scan on contact_reports_p2019_04_10_geopoint_idx (cost=0.00..31.23 rows=908 width=0) (actual time=307.713..307.713 rows=1,332,172 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared hit=8242
8. 0.067 0.067 ↓ 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.067..0.067 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)))
  • 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)))
  • Buffers: shared hit=1
10. 0.029 0.029 ↓ 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.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)))
  • Buffers: shared hit=1
11. 0.033 0.033 ↓ 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.033..0.033 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)))
  • Buffers: shared hit=1
12. 0.035 0.035 ↓ 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.035..0.035 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)))
  • 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.030..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)))
  • Buffers: shared hit=1
14. 81.674 81.696 ↓ 1.1 10 81,696

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

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

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

  • Buffers: shared hit=1
16. 408.480 408.480 ↑ 1.0 1 81,696

Index Scan using pk_identities on identities i (cost=0.42..0.47 rows=1 width=247) (actual time=0.005..0.005 rows=1 loops=81,696)

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