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: Thu, 27 Aug 2015 11:21:12 +0200 (CEST)
Message-ID: <alpine.DEB.2.11.1508271118020.3739@pyrite> (raw)
In-Reply-To: <CAE2gYzz-QDpkqPJirVNUsKDLu+jJ_CaqN2UyZ+ayZ4Pec+1O3g@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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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.1508271118020.3739@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