Re: Gist indexing performance with cidr types

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