performance issue

[lethalwp]

Hello,

I have a big performance issue like #1077
Tried to up the RAM, postgres buffers, couldn't find anything working.

This performance problem happens when, as it is in our network:
split a xx.0.0.0/8 in 2000 /24 subnets

When i then "open" the scope in the webinterface, it is a unusable waittime.

this is the faulty query :
nipap=> SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
pid | age | usename | query
-------+-----------------+----------+--------------------------------------------------------------------
21579 | | |
21581 | | postgres |
21577 | | |
21576 | | |
21578 | | |
21583 | 00:00:01.958938 | nipap | +
| | | SELECT +
| | | id, +
| | | vrf_id, +
| | | vrf_rt, +
| | | vrf_name, +
| | | family, +
| | | display, +
| | | match, +
| | | prefix, +
| | | prefix_length, +
| | | display_prefix::text AS display_prefix, +
| | | description, +
| | | comment, +
| | | inherited_tags, +
| | | tags, +
| | | node, +
| | | pool_id, +
| | | pool_name, +
| | | type, +
| | | status, +
| | | indent, +
| | | country, +
| | | order_id, +
| | | customer_id, +
| | | external_key, +
| | | authoritative_source, +
| | | alarm_priority, +
| | | monitor, +
| | | vlan, +
| | | added, +
| | | last_modified, +
| | | children, +
| | | total_addresses, +
| | | used_addresses, +
| | | free_addresses, +
| | | avps, +
| | | expires +
| | | FROM ( +
| | | SELECT DISTINCT ON(vrf_rt_order(vrf.rt), p1.prefix) p1.id,+
| | | p1.prefix, +
| | | p1.display_prefix, +
| | | p1.description, +
| | | p1.comment, +
| | | COALESCE(p1.inherited_tags, '{}') AS inherited_tags, +
| | | COALESCE(p1.tags, '{}') AS tags, +
| | | p1.node, +
| | |
(6 rows)

nipap=>

What should i do?

[lethalwp]

so let me resume: the already mentionned 1077
#1095
#1169

are all the same problem:
when there are too many subnets (or hosts) in a subnet, the query will take too much time.

This issue is already several years old.

As sexy as NIPAP is, it doesn't look to be scalable.