explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EyMh

Settings
# exclusive inclusive rows x rows loops node
1. 28.380 3,421.856 ↓ 22.8 217,655 1

Limit (cost=9,361.43..9,385.27 rows=9,537 width=295) (actual time=3,295.158..3,421.856 rows=217,655 loops=1)

  • Buffers: shared hit=1033708, temp read=7744 written=7745
2. 579.013 3,393.476 ↓ 22.8 217,655 1

Sort (cost=9,361.43..9,385.27 rows=9,537 width=295) (actual time=3,295.154..3,393.476 rows=217,655 loops=1)

  • Sort Method: external merge Disk: 61952kB
  • Buffers: shared hit=1033708, temp read=7744 written=7745
3. 599.802 2,814.463 ↓ 22.8 217,655 1

Nested Loop (cost=21.94..8,731.07 rows=9,537 width=295) (actual time=611.306..2,814.463 rows=217,655 loops=1)

  • Buffers: shared hit=1033708
4. 140.988 1,344.041 ↓ 22.8 217,655 1

Hash Join (cost=21.51..1,944.10 rows=9,537 width=279) (actual time=611.255..1,344.041 rows=217,655 loops=1)

  • Buffers: shared hit=163088
5. 68.973 1,203.007 ↓ 22.8 217,655 1

Nested Loop (cost=19.99..1,912.94 rows=9,537 width=260) (actual time=611.176..1,203.007 rows=217,655 loops=1)

  • Buffers: shared hit=163087
6. 0.013 0.013 ↑ 1.0 1 1

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

  • Filter: (username = 'cmaoi'::text)
  • Buffers: shared hit=1
7. 36.943 1,134.021 ↓ 3,248.6 217,655 1

Append (cost=19.99..1,911.23 rows=67 width=260) (actual time=611.159..1,134.021 rows=217,655 loops=1)

  • Buffers: shared hit=163086
8. 869.489 1,097.003 ↓ 3,568.1 217,655 1

Bitmap Heap Scan on contact_reports_p2019_06_12 cr (cost=19.99..1,861.84 rows=61 width=260) (actual time=611.153..1,097.003 rows=217,655 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=163080
9. 227.514 227.514 ↓ 1,842.1 875,002 1

Bitmap Index Scan on contact_reports_p2019_06_12_geopoint_idx (cost=0.00..19.98 rows=475 width=0) (actual time=227.514..227.514 rows=875,002 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared hit=5625
10. 0.019 0.019 ↓ 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.018..0.019 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.011 0.011 ↓ 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.011..0.011 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.011 0.011 ↓ 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.011..0.011 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.012 0.012 ↓ 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.012..0.012 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.011 0.011 ↓ 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.011..0.011 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.011 0.011 ↓ 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.011..0.011 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.019 0.046 ↓ 1.1 26 1

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

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

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

  • Buffers: shared hit=1
18. 870.620 870.620 ↑ 1.0 1 217,655

Index Scan using pk_identities on identities i (cost=0.43..0.69 rows=1 width=204) (actual time=0.004..0.004 rows=1 loops=217,655)

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