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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
