explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HVAW

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 37.148 10,862.634 ↑ 1.0 500,000 1

Limit (cost=167,228.17..261,388.66 rows=500,000 width=294) (actual time=7,253.94..10,862.634 rows=500,000 loops=1)

  • Buffers: shared hit=589,067 read=150,793 dirtied=1 written=4,374, temp read=28,150 written=78,464
2. 540.067 10,825.486 ↑ 1.2 500,000 1

Hash Join (cost=167,228.17..278,992.53 rows=593,478 width=294) (actual time=7,253.938..10,825.486 rows=500,000 loops=1)

  • Buffers: shared hit=589,067 read=150,793 dirtied=1 written=4,374, temp read=28,150 written=78,464
3. 1,254.385 10,285.399 ↑ 1.2 500,000 1

Hash Join (cost=167,226.65..265,276.51 rows=593,478 width=430) (actual time=7,253.853..10,285.399 rows=500,000 loops=1)

  • Buffers: shared hit=589,066 read=150,793 dirtied=1 written=4,374, temp read=28,150 written=78,464
4. 293.725 7,505.099 ↓ 2.2 1,312,181 1

Nested Loop (cost=0.44..6,401.42 rows=593,478 width=254) (actual time=5,725.563..7,505.099 rows=1,312,181 loops=1)

  • Buffers: shared hit=515,244 read=150,793 written=4,374
5. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on geoserver_acl g (cost=0..1.04 rows=1 width=146) (actual time=0.006..0.009 rows=1 loops=1)

  • Filter: (g.username = 'cmaoi'::text)
  • Buffers: shared hit=1
6. 88.625 7,211.365 ↓ 4,789.0 1,312,181 1

Append (cost=0.44..6,397.64 rows=274 width=254) (actual time=5,725.55..7,211.365 rows=1,312,181 loops=1)

  • Buffers: shared hit=515,243 read=150,793 written=4,374
7. 0.015 0.015 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_09_30_received_date_time_idx on contact_reports_p2019_09_30 cr (cost=0.44..4.46 rows=1 width=253) (actual time=0.014..0.015 rows=0 loops=1)

  • Index Cond: (cr.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Filter: (cr.geopoint && g.geometry)
  • Buffers: shared hit=3
8. 5,353.921 7,122.378 ↓ 4,914.5 1,312,181 1

Bitmap Heap Scan on contact_reports_p2019_10_01 cr_1 (cost=52.72..6,342.86 rows=267 width=254) (actual time=5,725.529..7,122.378 rows=1,312,181 loops=1)

  • Filter: (cr_1.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Heap Blocks: exact=618,835
  • Buffers: shared hit=515,240 read=150,787 written=4,374
9. 1,768.457 1,768.457 ↓ 4,846.6 7,904,837 1

Bitmap Index Scan on contact_reports_p2019_10_01_geopoint_idx (cost=0..52.65 rows=1,631 width=0) (actual time=1,768.457..1,768.457 rows=7,904,837 loops=1)

  • Index Cond: (cr_1.geopoint && g.geometry)
  • Buffers: shared hit=47,192
10. 0.083 0.083 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_10_02_geopoint_idx on contact_reports_p2019_10_02 cr_2 (cost=0.14..8.16 rows=1 width=236) (actual time=0.083..0.083 rows=0 loops=1)

  • Index Cond: (cr_2.geopoint && g.geometry)
  • Filter: (cr_2.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Buffers: shared read=1
11. 0.068 0.068 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_10_03_geopoint_idx on contact_reports_p2019_10_03 cr_3 (cost=0.14..8.16 rows=1 width=236) (actual time=0.067..0.068 rows=0 loops=1)

  • Index Cond: (cr_3.geopoint && g.geometry)
  • Filter: (cr_3.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Buffers: shared read=1
12. 0.050 0.050 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_10_04_geopoint_idx on contact_reports_p2019_10_04 cr_4 (cost=0.14..8.16 rows=1 width=236) (actual time=0.05..0.05 rows=0 loops=1)

  • Index Cond: (cr_4.geopoint && g.geometry)
  • Filter: (cr_4.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Buffers: shared read=1
13. 0.046 0.046 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_10_05_geopoint_idx on contact_reports_p2019_10_05 cr_5 (cost=0.14..8.16 rows=1 width=236) (actual time=0.046..0.046 rows=0 loops=1)

  • Index Cond: (cr_5.geopoint && g.geometry)
  • Filter: (cr_5.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Buffers: shared read=1
14. 0.051 0.051 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_10_06_geopoint_idx on contact_reports_p2019_10_06 cr_6 (cost=0.14..8.16 rows=1 width=236) (actual time=0.051..0.051 rows=0 loops=1)

  • Index Cond: (cr_6.geopoint && g.geometry)
  • Filter: (cr_6.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Buffers: shared read=1
15. 0.049 0.049 ↓ 0.0 0 1

Index Scan using contact_reports_default_geopoint_idx on contact_reports_default cr_7 (cost=0.14..8.16 rows=1 width=236) (actual time=0.049..0.049 rows=0 loops=1)

  • Index Cond: (cr_7.geopoint && g.geometry)
  • Filter: (cr_7.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Buffers: shared read=1
16. 834.630 1,525.915 ↑ 1.0 1,949,040 1

Hash (cost=93,325.65..93,325.65 rows=1,950,365 width=180) (actual time=1,525.915..1,525.915 rows=1,949,040 loops=1)

  • Buffers: shared hit=73,822 dirtied=1, temp written=39,539
17. 691.285 691.285 ↑ 1.0 1,949,040 1

Seq Scan on identities i (cost=0..93,325.65 rows=1,950,365 width=180) (actual time=0.016..691.285 rows=1,949,040 loops=1)

  • Buffers: shared hit=73,822 dirtied=1
18. 0.009 0.020 ↓ 1.1 26 1

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

  • Buffers: shared hit=1
19. 0.011 0.011 ↓ 1.1 26 1

Seq Scan on providers p (cost=0..1.23 rows=23 width=23) (actual time=0.006..0.011 rows=26 loops=1)

  • Buffers: shared hit=1