explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p6SM

Settings
# exclusive inclusive rows x rows loops node
1. 27.500 3,427.145 ↓ 22.9 216,980 1

Limit (cost=9,315.38..9,339.03 rows=9,459 width=295) (actual time=3,326.105..3,427.145 rows=216,980 loops=1)

  • Buffers: shared hit=1030908, temp read=7719 written=7720
2. 566.531 3,399.645 ↓ 22.9 216,980 1

Sort (cost=9,315.38..9,339.03 rows=9,459 width=295) (actual time=3,326.101..3,399.645 rows=216,980 loops=1)

  • Sort Method: external merge Disk: 61752kB
  • Buffers: shared hit=1030908, temp read=7719 written=7720
3. 643.701 2,833.114 ↓ 22.9 216,980 1

Nested Loop (cost=21.93..8,690.74 rows=9,459 width=295) (actual time=599.533..2,833.114 rows=216,980 loops=1)

  • Buffers: shared hit=1030908
4. 137.129 1,321.493 ↓ 22.9 216,980 1

Hash Join (cost=21.50..1,940.07 rows=9,459 width=279) (actual time=599.482..1,321.493 rows=216,980 loops=1)

  • Buffers: shared hit=162988
5. 69.232 1,184.311 ↓ 22.9 216,980 1

Nested Loop (cost=19.99..1,909.15 rows=9,459 width=260) (actual time=599.397..1,184.311 rows=216,980 loops=1)

  • Buffers: shared hit=162987
6. 0.014 0.014 ↑ 1.0 1 1

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

  • Filter: (username = 'cmaoi'::text)
  • Buffers: shared hit=1
7. 35.140 1,115.065 ↓ 3,099.7 216,980 1

Append (cost=19.99..1,907.42 rows=70 width=260) (actual time=599.379..1,115.065 rows=216,980 loops=1)

  • Buffers: shared hit=162986
8. 867.384 1,079.781 ↓ 3,390.3 216,980 1

Bitmap Heap Scan on contact_reports_p2019_06_12 cr (cost=19.99..1,858.02 rows=64 width=260) (actual time=599.373..1,079.781 rows=216,980 loops=1)

  • Filter: ((report_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=162980
9. 212.397 212.397 ↓ 1,844.6 874,326 1

Bitmap Index Scan on contact_reports_p2019_06_12_geopoint_idx (cost=0.00..19.97 rows=474 width=0) (actual time=212.397..212.397 rows=874,326 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared hit=5623
10. 0.033 0.033 ↓ 0.0 0 1

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

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

Index Scan using contact_reports_p2019_06_14_geopoint_idx on contact_reports_p2019_06_14 cr_2 (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: ((report_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
12. 0.021 0.021 ↓ 0.0 0 1

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

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

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

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time >= '2019-06-12 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_17_geopoint_idx on contact_reports_p2019_06_17 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: ((report_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
15. 0.018 0.018 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=1
17. 0.029 0.029 ↓ 1.1 26 1

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

  • Buffers: shared hit=1
18. 867.920 867.920 ↑ 1.0 1 216,980

Index Scan using pk_identities on identities i (cost=0.43..0.69 rows=1 width=202) (actual time=0.004..0.004 rows=1 loops=216,980)

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