VBR-Info: md=nordu.net; mc=all; mv=swamid.se
Date: Wed, 26 Aug 2015 09:58:10 +0200 (CEST)
From: Henrik Thostrup Jensen <htj@nordu.net>
To: Emre Hasegeli <emre@hasegeli.com>,
 pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Gist indexing performance with cidr types
In-Reply-To: 
 <CAE2gYzyv8zCeKUKyE8EcZcVtdTFeZaUucRjRqmtzu2jT5EQQyQ@mail.gmail.com>
Message-ID: <alpine.DEB.2.11.1508260940060.35330@pyrite>
References: <alpine.DEB.2.11.1508251504160.31004@pyrite>
 <CAE2gYzyv8zCeKUKyE8EcZcVtdTFeZaUucRjRqmtzu2jT5EQQyQ@mail.gmail.com>
User-Agent: Alpine 2.11 (DEB 23 2013-08-11)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
Received-SPF: neutral (e-mailfilter02.sunet.se: 109.105.110.42 is neither
 permitted nor denied by domain htj@nordu.net)
 receiver=e-mailfilter02.sunet.se; client-ip=109.105.110.42;
 envelope-from=<htj@nordu.net>; helo=smtp1.nordu.net;
 identity=mailfrom
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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