explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vXXV

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 25.450 3,212.614 ↓ 720.3 199,511 1

Limit (cost=5,663.13..5,663.82 rows=277 width=296) (actual time=3,116.638..3,212.614 rows=199,511 loops=1)

2. 533.572 3,187.164 ↓ 720.3 199,511 1

Sort (cost=5,663.13..5,663.82 rows=277 width=296) (actual time=3,116.633..3,187.164 rows=199,511 loops=1)

  • Sort Method: external merge Disk: 56808kB
3. 501.559 2,653.592 ↓ 720.3 199,511 1

Hash Join (cost=2.38..5,651.89 rows=277 width=296) (actual time=583.075..2,653.592 rows=199,511 loops=1)

4. 213.300 2,152.001 ↓ 720.3 199,511 1

Nested Loop (cost=0.86..5,643.97 rows=277 width=460) (actual time=582.988..2,152.001 rows=199,511 loops=1)

5. 60.087 1,140.657 ↓ 720.3 199,511 1

Nested Loop (cost=0.43..5,488.96 rows=277 width=261) (actual time=582.945..1,140.657 rows=199,511 loops=1)

6. 0.015 0.015 ↑ 3.0 1 1

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

  • Filter: (username = 'cmaoi'::text)
7. 32.913 1,080.555 ↓ 1,366.5 199,511 1

Append (cost=0.43..1,822.57 rows=146 width=261) (actual time=582.926..1,080.555 rows=199,511 loops=1)

8. 0.010 0.010 ↓ 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.010..0.010 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)))
9. 874.923 1,047.465 ↓ 1,435.3 199,511 1

Bitmap Heap Scan on contact_reports_p2019_06_12 cr_1 (cost=15.87..1,780.32 rows=139 width=261) (actual time=582.911..1,047.465 rows=199,511 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)))
10. 172.542 172.542 ↓ 1,837.7 837,997 1

Bitmap Index Scan on contact_reports_p2019_06_12_geopoint_idx (cost=0.00..15.83 rows=456 width=0) (actual time=172.542..172.542 rows=837,997 loops=1)

  • Index Cond: (geopoint && g.geometry)
11. 0.047 0.047 ↓ 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..5.51 rows=1 width=236) (actual time=0.047..0.047 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)))
12. 0.020 0.020 ↓ 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..5.51 rows=1 width=236) (actual time=0.020..0.020 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)))
13. 0.018 0.018 ↓ 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..5.51 rows=1 width=236) (actual time=0.018..0.018 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)))
14. 0.029 0.029 ↓ 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..5.51 rows=1 width=236) (actual time=0.029..0.029 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)))
15. 0.027 0.027 ↓ 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..5.51 rows=1 width=236) (actual time=0.027..0.027 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)))
16. 0.026 0.026 ↓ 0.0 0 1

Index Scan using contact_reports_default_geopoint_idx on contact_reports_default cr_7 (cost=0.14..5.51 rows=1 width=236) (actual time=0.026..0.026 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)))
17. 798.044 798.044 ↑ 1.0 1 199,511

Index Scan using pk_identities on identities i (cost=0.43..0.56 rows=1 width=203) (actual time=0.004..0.004 rows=1 loops=199,511)

  • Index Cond: (identity_id = cr.identity_id)
18. 0.012 0.032 ↓ 1.1 26 1

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

19. 0.020 0.020 ↓ 1.1 26 1

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