Re: Gist indexing performance with cidr types

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