> I'm trying to get a query to run fast enough for interactive use. I've gotten
> some speed-up, but still not there. It is for a tool called IRRExplorer
> (http://irrexplorer.nlnog.net/) that correlates IP routes between Internet
> Route Registries and real-world routing information. We landed on PostgreSQL
> largely due to indexing of the cidr type with gist indexing.
It is nice to hear about someone making use of the feature.
> * Query
>
> When a user inputs an AS number a simple match on the asn column will return
> the stuff relevant. However, the interesting thing to display is
> conflicting/rogue routes. This means matching routes with the && operator to
> find all covered/covering routes. This would look something like this:
>
> SELECT rv.route, rv.asn, rv.source
> FROM routes_view rv
> LEFT OUTER JOIN routes_view r ON (rv.route && r.route)
> WHERE rv.route && r.route AND r.asn = %s
Why don't you just use INNER JOIN like this:
SELECT rv.route, rv.asn, rv.source
FROM routes_view rv
JOIN routes_view r ON rv.route && r.route
WHERE r.asn = %s
> While this is fairly fast if the initial set of routes is relatively small
> (<100) it runs with a second or so, but if the number of routes matching the
> asn is large (>1000), it takes quite a while (+30 seconds).Explain analyze
> link:
>
> http://explain.depesz.com/s/dHqo
>
> I am not terribly good at reading the output, but it seem most of the time is
> actually spend on the bitmap scan for the gist index. It there another type of
> indexing that would behave better here?
An index to the "asn" column would probably help to the outer side,
but more time seems to be consumed on the inner side. Plain index
scan would probably be faster for it. You can test it by setting
enable_bitmapscan to false.
The problem about bitmap index scan is selectivity estimation. The
planner estimates a lot more rows would match the condition, so it
chooses bitmap index scan. Selectivity estimation functions for inet
on PostgreSQL 9.4 just return some constants, so it is expected. We
developed better ones for 9.5. PostgreSQL 9.5 also supports index
only scans with GiST which can be even better than plain index scan.
Can you try 9.5 to see if they help?