Comment 4 for bug 1702669

I took a closer look at the EXPLAIN ANALYZE command. I think you can indeed speed up that query by adding an index.

In summary, I think if you add the following index, you'll get the most improvement:

    CREATE INDEX maasserver_staticipaddress__ip__family__idx ON maasserver_staticipaddress (family(ip));

---

Long explanation:

First, you can run EXPLAIN ANALYZE as follows on the view (no need to paste in the giant SQL snippet that describes the view):

    EXPLAIN ANALYZE SELECT * from maasserver_routable_pairs;

On my production MAAS at home, that query originally executed in ~10ms on the average. After adding that particular index, query execution time dropped to ~3.5ms.

I looked at the join conditions in the EXPLAIN ANALYZE output and tested adding the following indexes:

CREATE INDEX maasserver_staticipaddress__ip__family__idx ON maasserver_staticipaddress (family(ip));
CREATE INDEX maasserver_vlan__space_id__idx ON maasserver_vlan (space_id);
CREATE INDEX maasserver_staticipaddress__subnet_id__idx ON maasserver_staticipaddress (subnet_id);
CREATE INDEX maasserver_staticipaddress__ip__is_not_null__idx ON maasserver_staticipaddress (ip) where (ip IS NOT NULL);

Then I dropped the indexes, on the off chance that we decide to create them [with the same name] from MAAS code at a later date:

DROP INDEX maasserver_staticipaddress__ip__family__idx;
DROP INDEX maasserver_vlan__space_id__idx;
DROP INDEX maasserver_staticipaddress__subnet_id__idx;
DROP INDEX maasserver_staticipaddress__ip__is_not_null__idx;

All of my tests indicated that the index on "family(ip)" made the most significant impact.

The other two indexes may speed up the query as well, but the benefit seems marginal compared to indexing the address family of each IP address (which affects the decision about every row in that view, and is a derived value that must otherwise be computed every time).