PostgreSQL Count occurrences of contained IP in subnets list
PostgreSQL Count occurrences of contained IP in subnets list
I have a PostgreSQL 9.5 DB set-up with two tables.
t_hosts
t_subnets
t_hosts contains a list of ip addresses and t_subnets a list of subnets.
t_hosts
t_subnets
I want to know for each subnet in t_subnets the number of contained (>>) ip address from t_hosts.
t_subnets
>>
t_hosts
I've put together an extremely inefficient request with a nested count. I'm looking for a more elegant and optimised way of addressing this problem (my current solution is too slow).
Current solution:
CREATE MATERIALIZED VIEW example.mv_count_hosts_in_subnets AS
WITH hosts AS (
SELECT host FROM example.t_hosts
)
SELECT subnet, (SELECT COUNT(host) FROM hosts WHERE subnet >> host) FROM
example.t_subnets WITH NO DATA
Any idea?
The CTE (
with ...) is completely useless and likely to make things slower. Just use t_hosts directly inside your scalar sub-query– a_horse_with_no_name
Aug 23 at 11:49
with ...
t_hosts
What data types are
subnet and host? Posting the complete CREATE statements of the tables wouldn't harm either.– sticky bit
Aug 23 at 12:05
subnet
host
CREATE
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Please provide sample data, desired results, and some explanation of what "ineffiicient" means.
– Gordon Linoff
Aug 23 at 10:46