explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FXxY

Settings
# exclusive inclusive rows x rows loops node
1. 28.004 3,562.639 ↓ 22.9 215,873 1

Limit (cost=9,299.60..9,323.19 rows=9,435 width=295) (actual time=3,461.047..3,562.639 rows=215,873 loops=1)

  • Buffers: shared hit=1026118 dirtied=415, temp read=7680 written=7681
2. 566.304 3,534.635 ↓ 22.9 215,873 1

Sort (cost=9,299.60..9,323.19 rows=9,435 width=295) (actual time=3,461.043..3,534.635 rows=215,873 loops=1)

  • Sort Method: external merge Disk: 61440kB
  • Buffers: shared hit=1026118 dirtied=415, temp read=7680 written=7681
3. 693.859 2,968.331 ↓ 22.9 215,873 1

Nested Loop (cost=21.92..8,676.71 rows=9,435 width=295) (actual time=645.561..2,968.331 rows=215,873 loops=1)

  • Buffers: shared hit=1026118 dirtied=415
4. 144.862 1,410.980 ↓ 22.9 215,873 1

Hash Join (cost=21.50..1,937.30 rows=9,435 width=279) (actual time=645.505..1,410.980 rows=215,873 loops=1)

  • Buffers: shared hit=162626
5. 69.569 1,266.064 ↓ 22.9 215,873 1

Nested Loop (cost=19.98..1,906.46 rows=9,435 width=260) (actual time=645.409..1,266.064 rows=215,873 loops=1)

  • Buffers: shared hit=162625
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.877 1,196.481 ↓ 3,083.9 215,873 1

Append (cost=19.98..1,904.72 rows=70 width=260) (actual time=645.391..1,196.481 rows=215,873 loops=1)

  • Buffers: shared hit=162624
8. 951.889 1,160.435 ↓ 3,373.0 215,873 1

Bitmap Heap Scan on contact_reports_p2019_06_12 cr (cost=19.98..1,855.31 rows=64 width=260) (actual time=645.385..1,160.435 rows=215,873 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 (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=162618
9. 208.546 208.546 ↓ 1,844.0 872,189 1

Bitmap Index Scan on contact_reports_p2019_06_12_geopoint_idx (cost=0.00..19.96 rows=473 width=0) (actual time=208.546..208.546 rows=872,189 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared hit=5608
10. 0.037 0.037 ↓ 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.037..0.037 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 (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
11. 0.021 0.021 ↓ 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.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 (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
12. 0.027 0.027 ↓ 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.027..0.027 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 (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
13. 0.033 0.033 ↓ 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.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 (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
14. 0.031 0.031 ↓ 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.031..0.031 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 (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
15. 0.020 0.020 ↓ 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.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 (received_date_time <= '2019-06-13 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=1
16. 0.030 0.054 ↓ 1.1 26 1

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

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

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

  • Buffers: shared hit=1
18. 863.492 863.492 ↑ 1.0 1 215,873

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=215,873)

  • Index Cond: (identity_id = cr.identity_id)
  • Buffers: shared hit=863492 dirtied=415