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?





Please provide sample data, desired results, and some explanation of what "ineffiicient" means.
– Gordon Linoff
Aug 23 at 10:46





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.

-

Popular posts from this blog

PHP code is not being executed, instead code shows on the page

Image
266 58 I'm trying to execute some PHP code on a project (using Dreamweaver) but the code isn't being run. When I check the source code, the PHP code appears as HTML tags (I can see it in the source code). Apache is running properly (I'm working with XAMPP), the PHP pages are being opened properly but the PHP code isn't being executed. Does someone have a suggestion about what is happening? Note: The file is already named as filename.php Edit: The Code..: <? include_once("/code/configs.php"); ?> php apache share | improve this question edited Mar 22 '17 at 16:16 Taryn ♦ 191k 47 291 354 asked Feb 25 '11 at 19:06 Gui Gui 3,652 10 34 51 38 Are you using short tags <? instead of <?php ? – Dan Grossman Feb 25 '11 at 19:09 2 Yes, i'm using <? ... – Gui Feb 25 '11 at 19:12 22 Don't EVER use short tags. ( <? ). They are depre...
Read more

Administrative divisions of China

Image
Clash Royale CLAN TAG #URR8PPP For other uses, see Administrative divisions of China (disambiguation). This article is about the administrative divisions in the People's Republic of China. For those of the Republic of China commonly known as Taiwan, see Administrative divisions of the Republic of China. This article is part of a series on Administrative divisions of China Provincial level (1st) Municipalities Provinces Autonomous regions Special administrative regions Sub-provincial level Sub-provincial cities Sub-provincial autonomous prefectures Sub-provincial city districts Prefectural level (2nd) Prefectural cities Autonomous prefectures Leagues Prefectures (abolishing) Sub-prefectural-level Sub-prefectural cities Provincial-controlled cities Provincial-controlled counties Provincial-controlled districts County level (3rd) Counties Autonomous counties County-level cities Districts Ethnic districts Banners Autonomous banners Shennongjia Forestry District Liuzhi Special District...
Read more

Cardinality of the set of algorithms

Image
15 3 $begingroup$ Someone in a discussion brought up that (he reckons) there can be at least continuum number of strategies to approach a specific problem. The specific problem was trading strategies (not algorithms but strategies) but I think thats beside the point for my question. This got me thinking about the cardinality of the set of algorithms. I have been searching around a bit but have come up with nothing. I've been thinking that, since turing machines operate with a finite set of alphabet and the tape has to be indexable thus countable, it's impossible to have uncountable number of algorithms. My set theory is admittedly rusty so I am not certain at all my reasoning is valid and I probably wouldn't be able to prove it, but it's an interesting thought. What is the cardinality of the set of algorithms? algorithms turing-machines combinatorics share | cite | improve this question edited Aug 28 '18 at 13:59 Raphael ♦ 57.8k 24...
Read more