VBR-Info: md=nordu.net; mc=all; mv=swamid.se
Date: Thu, 27 Aug 2015 11:21:12 +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: 
 <CAE2gYzz-QDpkqPJirVNUsKDLu+jJ_CaqN2UyZ+ayZ4Pec+1O3g@mail.gmail.com>
Message-ID: <alpine.DEB.2.11.1508271118020.3739@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>
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

On Wed, 26 Aug 2015, Emre Hasegeli wrote:

> Can you try to isolate it even more by something like this:

I tried some different bisection approaches:

-- base query (time ~19 seconds)
EXPLAIN (ANALYZE, BUFFERS)
SELECT rv.route, rv.asn, rv.source
FROM
     (SELECT DISTINCT route FROM routes_view WHERE asn = 2914 AND [ stuff here ]) r
     JOIN routes_view rv ON (r.route && rv.route);

SELECT DISTINCT route FROM routes_view WHERE asn = 2914;    -> 732 rows, 0.2 seconds

masklen(route) <= 20;   -> 356 rows, join time  9.2 seconds
masklen(route) > 20;    -> 376 rows, join time  9.1 seconds

family(route) = 6       ->  22 rows, join time  0.2 seconds
family(route) = 4       -> 710 rows, join time 18.1 seconds

route <= '154.0.0.0'    -> 362 rows, join time 9.2 seconds
route > '154.0.0.0'     -> 370 rows, join time 9.5 seconds

Nothing really interesting here though.


> select * from routes where route && 'a.b.c.d/e';
>
> It would be easier to debug, if we can reproduce performance
> regression like this.  It would also be helpful to check where the
> time is spent.  Maybe "perf" on Linux would help, though I haven't
> used it before.

Haven't used this before either (but seem like a nice tool). Output while 
running the query:

Samples: 99K of event 'cpu-clock', Event count (approx.): 11396624870
   14.09%  postgres                         [.] inet_gist_consistent
   10.77%  postgres                         [.] 0x00000000000c05f7
   10.46%  postgres                         [.] FunctionCall5Coll
    5.68%  postgres                         [.] gistdentryinit
    5.57%  postgres                         [.] 0x00000000000c05c4
    4.62%  postgres                         [.] FunctionCall1Coll
    4.52%  postgres                         [.] MemoryContextReset
    4.25%  postgres                         [.] bitncmp
    3.32%  libc-2.19.so                     [.] __memcmp_sse4_1
    2.44%  postgres                         [.] 0x00000000000c08f9
    2.37%  postgres                         [.] 0x00000000000c0907
    2.27%  postgres                         [.] 0x00000000000c0682
    2.12%  postgres                         [.] pg_detoast_datum_packed
    1.86%  postgres                         [.] hash_search_with_hash_value
    1.40%  postgres                         [.] inet_gist_decompress
    1.09%  postgres                         [.] 0x00000000000c067e
    1.03%  postgres                         [.] 0x00000000000c047e
    0.77%  postgres                         [.] 0x00000000002f0e57
    0.75%  postgres                         [.] gistcheckpage

This seemed to stay reletively consistent throughout the query.


     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