From: | Henrik Thostrup Jensen <htj(at)nordu(dot)net> |
---|---|
To: | Emre Hasegeli <emre(at)hasegeli(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Gist indexing performance with cidr types |
Date: | 2015-08-26 07:58:10 |
Message-ID: | alpine.DEB.2.11.1508260940060.35330@pyrite |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Henrik Thostrup Jensen | 2015-08-26 09:00:38 | Re: Gist indexing performance with cidr types |
Previous Message | Emre Hasegeli | 2015-08-25 14:25:52 | Re: Gist indexing performance with cidr types |