From: Henrik Thostrup Jensen <htj@nordu.net>
To: Emre Hasegeli <emre@hasegeli.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Gist indexing performance with cidr types
Date: Wed, 26 Aug 2015 09:58:10 +0200 (CEST)
Message-ID: <alpine.DEB.2.11.1508260940060.35330@pyrite> (raw)
In-Reply-To: <CAE2gYzyv8zCeKUKyE8EcZcVtdTFeZaUucRjRqmtzu2jT5EQQyQ@mail.gmail.com>
References: <alpine.DEB.2.11.1508251504160.31004@pyrite>
<CAE2gYzyv8zCeKUKyE8EcZcVtdTFeZaUucRjRqmtzu2jT5EQQyQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: htj@nordu.net, emre@hasegeli.com
Subject: Re: Gist indexing performance with cidr types
In-Reply-To: <alpine.DEB.2.11.1508260940060.35330@pyrite>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox