Hi, thanks for the reply.
On Tue, 25 Aug 2015, Emre Hasegeli wrote:
>> 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.
Thanks to whoever made it. It is probably a niche-feature though.
>> 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
I probably have a habit of thinking in outer joins. The inner join turns
out to slightly slower though (but faster in planning), but it looks like
it depends on a dice roll by the planner (it does bitmap heap scan on
inner, and index scan on left outer).
>> 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,
"select route from routes where asn = %s" takes .15-.2 seconds on my
laptop, so it isn't where the time is spend here.
> 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.
This actually makes it go slower for inner join (31s -> 56s). Left outer
join is around the same.
> 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.
OK, that is interesting.
> Can you try 9.5 to see if they help?
I'll try installing it and report back.
Best regards, Henrik
Henrik Thostrup Jensen <htj at nordu.net>
Software Developer, NORDUnet
