Re: Gist indexing performance with cidr types - Mailing list pgsql-performance

From Henrik Thostrup Jensen
Subject Re: Gist indexing performance with cidr types
Date
Msg-id alpine.DEB.2.11.1508271118020.3739@pyrite
Whole thread Raw
In response to Re: Gist indexing performance with cidr types  (Emre Hasegeli <emre@hasegeli.com>)
Responses Re: Gist indexing performance with cidr types  (Emre Hasegeli <emre@hasegeli.com>)
List 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 by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: Index creation running now for 14 hours
Next
From: Henrik Thostrup Jensen
Date:
Subject: Re: Gist indexing performance with cidr types