From: Henrik Thostrup Jensen <htj@nordu.net>
To: Emre Hasegeli <emre@hasegeli.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andreas Karlsson <andreas@proxel.se>
Subject: Re: Gist indexing performance with cidr types
Date: Fri, 28 Aug 2015 16:05:14 +0200 (CEST)
Message-ID: <alpine.DEB.2.11.1508281513050.14499@pyrite> (raw)
In-Reply-To: <CAE2gYzz_Fr6PqHEN4dan_E5v+BGVyz4VSHdthxqQ2=Ay+F4_mQ@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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, jeff.janes@gmail.com, tgl@sss.pgh.pa.us, andreas@proxel.se
Subject: Re: Gist indexing performance with cidr types
In-Reply-To: <alpine.DEB.2.11.1508281513050.14499@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