explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OS6j : Optimization for: plan #SJZU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.839 5,528.990 ↑ 1.0 10,000 1

Limit (cost=6.24..59,668.75 rows=10,000 width=281) (actual time=3,593.539..5,528.990 rows=10,000 loops=1)

  • Buffers: shared hit=35745 read=141358
2. 58.096 5,526.151 ↑ 199.4 10,000 1

Nested Loop (cost=6.24..11,897,140.27 rows=1,994,072 width=281) (actual time=3,593.538..5,526.151 rows=10,000 loops=1)

  • Buffers: shared hit=35745 read=141358
3. 10.793 4,808.055 ↑ 199.4 10,000 1

Hash Join (cost=5.82..16,611.12 rows=1,994,072 width=816) (actual time=3,593.028..4,808.055 rows=10,000 loops=1)

  • Buffers: shared hit=5 read=137098
4. 5.511 4,797.239 ↑ 199.4 10,000 1

Nested Loop (cost=4.64..8,602.50 rows=1,994,072 width=807) (actual time=3,592.979..4,797.239 rows=10,000 loops=1)

  • Buffers: shared hit=4 read=137098
5. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on geoserver_acl g (cost=0.00..1.02 rows=1 width=185) (actual time=0.013..0.013 rows=1 loops=1)

  • Filter: (username = 'cmaoi'::text)
  • Buffers: shared hit=1
6. 2.584 4,791.715 ↓ 10.1 10,000 1

Append (cost=4.64..8,591.60 rows=987 width=807) (actual time=3,592.956..4,791.715 rows=10,000 loops=1)

  • Buffers: shared hit=3 read=137098
7. 899.107 1,484.801 ↓ 0.0 0 1

Bitmap Heap Scan on contact_reports_p2019_03_18 cr (cost=4.64..202.93 rows=23 width=797) (actual time=1,484.801..1,484.801 rows=0 loops=1)

  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
  • Buffers: shared hit=2 read=43991
8. 233.939 233.939 ↓ 3,203.5 147,362 1

Bitmap Index Scan on contact_reports_p2019_03_18_geopoint_idx (cost=0.00..4.63 rows=46 width=0) (actual time=233.939..233.939 rows=147,362 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared read=1008
9.          

SubPlan (forBitmap Heap Scan)

10. 351.755 351.755 ↑ 100.0 1 351,755

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.001..0.001 rows=1 loops=351,755)

11. 0.000 0.000 ↑ 1.0 1 351,755

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=351,755)

12. 0.300 0.300 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_03_19_geopoint_idx on contact_reports_p2019_03_19 cr_1 (cost=0.14..8.55 rows=1 width=220) (actual time=0.300..0.300 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
  • Buffers: shared read=1
13. 0.209 0.209 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_03_20_geopoint_idx on contact_reports_p2019_03_20 cr_2 (cost=0.14..8.55 rows=1 width=220) (actual time=0.209..0.209 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
  • Buffers: shared read=1
14. 0.112 0.112 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_03_21_geopoint_idx on contact_reports_p2019_03_21 cr_3 (cost=0.14..8.55 rows=1 width=220) (actual time=0.111..0.112 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
  • Buffers: shared read=1
15. 0.111 0.111 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_03_22_geopoint_idx on contact_reports_p2019_03_22 cr_4 (cost=0.14..8.55 rows=1 width=220) (actual time=0.111..0.111 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
  • Buffers: shared read=1
16. 0.221 0.221 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_03_23_geopoint_idx on contact_reports_p2019_03_23 cr_5 (cost=0.14..8.55 rows=1 width=220) (actual time=0.221..0.221 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
  • Buffers: shared read=1
17. 0.122 0.122 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_03_24_geopoint_idx on contact_reports_p2019_03_24 cr_6 (cost=0.14..8.55 rows=1 width=220) (actual time=0.122..0.122 rows=0 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
  • Buffers: shared read=1
18. 2,667.621 3,303.255 ↓ 64.9 10,000 1

Bitmap Heap Scan on contact_reports_p2019_03_25 cr_7 (cost=14.76..1,342.95 rows=154 width=807) (actual time=2,107.062..3,303.255 rows=10,000 loops=1)

  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
  • Buffers: shared hit=1 read=93101
19. 635.634 635.634 ↓ 1,587.1 488,822 1

Bitmap Index Scan on contact_reports_p2019_03_25_geopoint_idx (cost=0.00..14.72 rows=308 width=0) (actual time=635.634..635.634 rows=488,822 loops=1)

  • Index Cond: (geopoint && g.geometry)
  • Buffers: shared read=3411
20. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_03_26_geopoint_idx on contact_reports_p2019_03_26 cr_8 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
21. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_p2019_03_27 cr_9 (cost=20.88..2,543.43 rows=293 width=805) (never executed)

  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
22. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_p2019_03_27_geopoint_idx (cost=0.00..20.80 rows=585 width=0) (never executed)

  • Index Cond: (geopoint && g.geometry)
23. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_p2019_03_28 cr_10 (cost=9.99..902.49 rows=103 width=803) (never executed)

  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
24. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_p2019_03_28_geopoint_idx (cost=0.00..9.96 rows=207 width=0) (never executed)

  • Index Cond: (geopoint && g.geometry)
25. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_p2019_03_29 cr_11 (cost=26.36..3,389.77 rows=390 width=810) (never executed)

  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
26. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_p2019_03_29_geopoint_idx (cost=0.00..26.27 rows=780 width=0) (never executed)

  • Index Cond: (geopoint && g.geometry)
27. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_03_30_geopoint_idx on contact_reports_p2019_03_30 cr_12 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
28. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_03_31_geopoint_idx on contact_reports_p2019_03_31 cr_13 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
29. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_01_geopoint_idx on contact_reports_p2019_04_01 cr_14 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
30. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_02_geopoint_idx on contact_reports_p2019_04_02 cr_15 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
31. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_03_geopoint_idx on contact_reports_p2019_04_03 cr_16 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
32. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_04_geopoint_idx on contact_reports_p2019_04_04 cr_17 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
33. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_05_geopoint_idx on contact_reports_p2019_04_05 cr_18 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
34. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_06_geopoint_idx on contact_reports_p2019_04_06 cr_19 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
35. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_07_geopoint_idx on contact_reports_p2019_04_07 cr_20 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
36. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_08_geopoint_idx on contact_reports_p2019_04_08 cr_21 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
37. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_09_geopoint_idx on contact_reports_p2019_04_09 cr_22 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
38. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_10_geopoint_idx on contact_reports_p2019_04_10 cr_23 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
39. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_11_geopoint_idx on contact_reports_p2019_04_11 cr_24 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
40. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_12_geopoint_idx on contact_reports_p2019_04_12 cr_25 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
41. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_13_geopoint_idx on contact_reports_p2019_04_13 cr_26 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
42. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_p2019_04_14_geopoint_idx on contact_reports_p2019_04_14 cr_27 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
43. 0.000 0.000 ↓ 0.0 0 0

Index Scan using contact_reports_default_geopoint_idx on contact_reports_default cr_28 (cost=0.14..8.55 rows=1 width=220) (never executed)

  • Index Cond: (geopoint && g.geometry)
  • Filter: ((report_date_time > '2019-03-18 08:00:00'::timestamp without time zone) AND (SubPlan 1))
44. 0.008 0.023 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=13) (actual time=0.023..0.023 rows=8 loops=1)

  • Buffers: shared hit=1
45. 0.015 0.015 ↑ 1.0 8 1

Seq Scan on providers p (cost=0.00..1.08 rows=8 width=13) (actual time=0.011..0.015 rows=8 loops=1)

  • Buffers: shared hit=1
46. 660.000 660.000 ↑ 1.0 1 10,000

Index Scan using pk_identities on identities i (cost=0.42..5.94 rows=1 width=299) (actual time=0.066..0.066 rows=1 loops=10,000)

  • Index Cond: (identity_id = cr.identity_id)
  • Buffers: shared hit=35740 read=4260