explain.depesz.com

PostgreSQL's explain analyze made readable

Result: maEZ : Optimization for: plan #vXXV

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 27.077 4,000.357 ↓ 9.9 214,678 1

Limit (cost=15,967.72..16,021.95 rows=21,691 width=295) (actual time=3,896.173..4,000.357 rows=214,678 loops=1)

  • Buffers: shared hit=1020358 dirtied=94, temp read=7638 written=7639
2. 710.463 3,973.280 ↓ 9.9 214,678 1

Sort (cost=15,967.72..16,021.95 rows=21,691 width=295) (actual time=3,896.170..3,973.280 rows=214,678 loops=1)

  • Sort Method: external merge Disk: 61104kB
  • Buffers: shared hit=1020358 dirtied=94, temp read=7638 written=7639
3. 634.995 3,262.817 ↓ 9.9 214,678 1

Nested Loop (cost=2.38..14,405.45 rows=21,691 width=295) (actual time=714.384..3,262.817 rows=214,678 loops=1)

  • Buffers: shared hit=1020358 dirtied=94
4. 153.190 1,554.432 ↓ 9.9 214,678 1

Hash Join (cost=1.95..1,966.98 rows=21,691 width=279) (actual time=714.256..1,554.432 rows=214,678 loops=1)

  • Buffers: shared hit=161646
5. 77.077 1,401.137 ↓ 9.9 214,678 1

Nested Loop (cost=0.43..1,898.03 rows=21,691 width=260) (actual time=713.973..1,401.137 rows=214,678 loops=1)

  • Buffers: shared hit=161645
6. 0.027 0.027 ↑ 1.0 1 1

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

  • Filter: (username = 'cmaoi'::text)
  • Buffers: shared hit=1
7. 39.983 1,324.033 ↓ 1,480.5 214,678 1

Append (cost=0.43..1,895.54 rows=145 width=260) (actual time=713.938..1,324.033 rows=214,678 loops=1)

  • Buffers: shared hit=161644
8. 0.013 0.013 ↓ 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..4.47 rows=1 width=258) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone)
  • Filter: ((geopoint && g.geometry) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=3
9. 1,060.725 1,283.812 ↓ 1,555.6 214,678 1

Bitmap Heap Scan on contact_reports_p2019_06_12 cr_1 (cost=19.97..1,841.32 rows=138 width=260) (actual time=713.915..1,283.812 rows=214,678 loops=1)

  • Filter: ((received_date_time >= '2019-06-12 08:00:00'::timestamp without time zone) AND (provider_id = ANY (g.providers)))
  • Buffers: shared hit=161635
10. 223.087 223.087 ↓ 1,846.4 867,828 1

Bitmap Index Scan on contact_reports_p2019_06_12_geopoint_idx (cost=0.00..19.94 rows=470 width=0) (actual time=223.087..223.087 rows=867,828 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared hit=5578
11. 0.059 0.059 ↓ 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..8.17 rows=1 width=236) (actual time=0.058..0.059 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((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.033 0.033 ↓ 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..8.17 rows=1 width=236) (actual time=0.033..0.033 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((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.033 0.033 ↓ 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..8.17 rows=1 width=236) (actual time=0.033..0.033 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((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.031 0.031 ↓ 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..8.17 rows=1 width=236) (actual time=0.031..0.031 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((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.033 0.033 ↓ 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..8.17 rows=1 width=236) (actual time=0.033..0.033 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((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.036 0.036 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=1
18. 0.063 0.063 ↓ 1.1 26 1

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

  • Buffers: shared hit=1
19. 1,073.390 1,073.390 ↑ 1.0 1 214,678

Index Scan using pk_identities on identities i (cost=0.43..0.55 rows=1 width=202) (actual time=0.005..0.005 rows=1 loops=214,678)

  • Index Cond: (identity_id = cr.identity_id)
  • Buffers: shared hit=858712 dirtied=94