Re: Gist indexing performance with cidr types
От | Henrik Thostrup Jensen |
---|---|
Тема | Re: Gist indexing performance with cidr types |
Дата | |
Msg-id | alpine.DEB.2.11.1508271118020.3739@pyrite обсуждение исходный текст |
Ответ на | Re: Gist indexing performance with cidr types (Emre Hasegeli <emre@hasegeli.com>) |
Ответы |
Re: Gist indexing performance with cidr types
(Emre Hasegeli <emre@hasegeli.com>)
|
Список | pgsql-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
В списке pgsql-performance по дате отправления: