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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
