perf: index `running` and `is_bridge` in `server_status`
We should add indexes to server_status
for columns running
and is_bridge
as these are heavily used for filtering rows by Network Status APIs. Currently those are not indexed so queries take a lot of time
i.e
SELECT
count(CASE WHEN is_bridge THEN 1 END) AS total_bridges,
count(CASE WHEN NOT is_bridge THEN 1 END) AS total_relays
FROM server_status;
○ Total Cost: 398,897.1
○ Planning Time: 1.12 ms
○ Execution Time: 947.77 ms
┬
│
└─⌠ Aggregate [Plain]
│ Groups records together based on a GROUP BY or
│ aggregate function (e.g. sum()).
│ ○ Duration: <1 ms (0%)
│ ○ Cost: 0.01999999996041879 (0%)
│ ○ Rows: 1
├► count(CASE WHEN is_bridge THEN 1 ELSE NULL::integer
│ END) + count(CASE WHEN (NOT is_bridge) THEN 1 ELSE
│ NULL::integer END)
│
└─⌠ Gather
│
│ ○ Duration: 54.07 ms (6%)
│ ○ Cost: 1,000.2000000000116 (0%)
│ ○ Rows: 3
│ rows Underestimated by 1.50x
├► (PARTIAL count(CASE WHEN is_bridge THEN 1 ELSE
│ NULL::integer END)) + (PARTIAL count(CASE WHEN (NOT
│ is_bridge) THEN 1 ELSE NULL::integer END))
│
└─⌠ Aggregate [Plain]
│ Groups records together based on a GROUP BY or
│ aggregate function (e.g. sum()).
│ ○ Duration: 583.38 ms (62%)
│ ○ Cost: 9,793.299999999988 (2%)
│ ○ Rows: 1
├► PARTIAL count(CASE WHEN is_bridge THEN 1 ELSE
│ NULL::integer END) + PARTIAL count(CASE WHEN (NOT
│ is_bridge) THEN 1 ELSE NULL::integer END)
│
└─⌠ Seq Scan slowest costliest largest
│ Finds relevant records by sequentially scanning
│ the input record set. When reading from a table,
│ Seq Scans (unlike Index Scans) perform a single
│ read operation (only the table is read).
│ ○ Duration: 1.01 s (212%)
│ ○ Cost: 388,103.58 (97%)
│ ○ Rows: 1,564,935
│ on public.server_status
│ rows Overestimated by 1.25x
⌡► is_bridge + published + nickname + fingerprint +
or_addresses + last_seen + first_seen + running
+ flags + country + country_name +
autonomous_system + as_name +
verified_host_names + last_restarted +
exit_policy + contacts + platform + version +
version_status + effective_family +
declared_family + transport +
bridgedb_distributor + blocklist +
last_changed_address_or_port + diff_or_addresses
+ unverified_host_names +
unreachable_or_addresses +
overload_ratelimits_version +
overload_ratelimits_timestamp +
overload_ratelimits_ratelimit +
overload_ratelimits_burstlimit +
overload_ratelimits_read_count +
overload_ratelimits_write_count +
overload_fd_exhausted_version +
overload_fd_exhausted_timestamp +
overload_general_timestamp +
overload_general_version