VBR-Info: md=nordu.net; mc=all; mv=swamid.se
Date: Fri, 28 Aug 2015 16:05:14 +0200 (CEST)
From: Henrik Thostrup Jensen <htj@nordu.net>
To: Emre Hasegeli <emre@hasegeli.com>
cc: pgsql-performance <pgsql-performance@postgresql.org>,
 Jeff Janes <jeff.janes@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 Andreas Karlsson <andreas@proxel.se>
Subject: Re: Gist indexing performance with cidr types
In-Reply-To: 
 <CAE2gYzz_Fr6PqHEN4dan_E5v+BGVyz4VSHdthxqQ2=Ay+F4_mQ@mail.gmail.com>
Message-ID: <alpine.DEB.2.11.1508281513050.14499@pyrite>
References: <alpine.DEB.2.11.1508251504160.31004@pyrite>
 <CAE2gYzyv8zCeKUKyE8EcZcVtdTFeZaUucRjRqmtzu2jT5EQQyQ@mail.gmail.com>
 <alpine.DEB.2.11.1508260940060.35330@pyrite>
 <alpine.DEB.2.11.1508261031400.35330@pyrite>
 <CAE2gYzxh0SVZe=nOsaLqfnNWKAi_HvDn4s+-J-co9aYwcw71Vw@mail.gmail.com>
 <alpine.DEB.2.11.1508261245250.35330@pyrite>
 <CAE2gYzz-QDpkqPJirVNUsKDLu+jJ_CaqN2UyZ+ayZ4Pec+1O3g@mail.gmail.com>
 <alpine.DEB.2.11.1508271118020.3739@pyrite>
 <CAE2gYzz_Fr6PqHEN4dan_E5v+BGVyz4VSHdthxqQ2=Ay+F4_mQ@mail.gmail.com>
User-Agent: Alpine 2.11 (DEB 23 2013-08-11)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; format=flowed; charset=US-ASCII
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

On Thu, 27 Aug 2015, Emre Hasegeli wrote:

> I think the slowdown is not related with the key your searched for,
> but the organisation of the index.  We have a simple structure for
> the index keys.  Basically, common bits of the child nodes are stored
> on the parent node.  It leads to not efficient indexes, where there
> are too much values with the same prefix.  I couldn't quite understand
> why it performs so bad, though.

I can see the issue. Unfortunately IP space tends to be fragmented in some 
ranges, and very sparse in other.

It is unfortunate that something to index IP prefixes doesn't handle BGP 
and IRR data very well (the only largish "real" datasets with IP prefixes 
I can think of).


> You might have better luck with ip4r extension [1] or creating an index 
> using the range types like this:
[snip]

Using the range type index:

  Nested Loop  (cost=0.42..603902.92 rows=8396377 width=26) (actual time=0.514..662.500 rows=8047 loops=1)
    ->  Seq Scan on hmm  (cost=0.00..11.32 rows=732 width=7) (actual time=0.015..0.119 rows=732 loops=1)
    ->  Index Scan using routes_cidr_to_range_idx on routes  (cost=0.42..595.58 rows=22941 width=19) (actual time=0.262..0.903 rows=11 loops=732)
          Index Cond: (inetrange(set_masklen((route)::inet, 0), set_masklen(broadcast((route)::inet), 0)) && inetrange(set_masklen((hmm.route)::inet, 0), set_masklen(broadcast((hmm.route)::inet), 0)))
  Planning time: 0.211 ms
  Execution time: 662.769 ms
(6 rows)

Boom. This is actually usefull.

It does take 70 seconds for the biggst network though. The index is also 
rather large:

  public | routes_cidr_to_range_idx | index | htj   | routes  | 158 MB |

Table is 119MB data. The gist index was 99 MB.


     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