Postgres - New key for hosts table

we did some performance testing in our dhcp setup and discovered, that the session setup rate is very bad (~ 20/sec.).

We use postgresql as host reservation backend and noticed, that it utilizes nearly 100% of the CPU during the test.

We discovered that the cause for that are pgsql queries inside the hosts database, which take about 7ms for each host.

There exist indexes for the "hosts" table, but none matches for the query that Kea uses in our case. So we added that index and the queries were about 400 times faster (session setup rate also over 200/sec., seems to be limited only by our dhcp relay, because the CPU utilization of the server stays below 50% now).

We did not test IPv6, but the problem could exist there as well.

In the case of IPv4, the following modifications of the pgsql database solved our problem:

added the following index: CREATE UNIQUE INDEX key_dhcp4_identifier on hosts (dhcp_identifier, dhcp_identifier_type);

--> there is already an index, but it contains the dhcp4_subnet_id, which Kea didn't use in the host lookup query.

modified the following existing index: "key_dhcp4_identifier_subnet_id" UNIQUE, btree (dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id) WHERE dhcp4_subnet_id IS NO T NULL AND dhcp4_subnet_id <> 0

"key_dhcp4_identifier_subnet_id" UNIQUE, btree (dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id) WHERE dhcp4_subnet_id IS NO T NULL

--> we use global reservations, where dhcp4_subnet_id = 0 is set in the hosts database, so the index was not used because of "dhcp4_subnet_id <> 0"

Some background information:

We use Kea to dynamically assign addresses out of pools inside a shared subnet, but only if the host has an entry in the "hosts" table. So the host entry does not have an IP address defined.

Please feel free to ask if you have further questions of if we can test/debug anything.