Improve Postgres storage list performance #2945

yannmh · 2017-06-29T17:55:50Z

Summary

Use || standard concatenation instead of the concat function to prevent sequential scans on the vault_kv_store table.

Issue

Long story short, we are running Vault 0.7.3 with the PostgreSQL physical backend, and have seen degraded performance with Vault List operations.
Investigating, we found out that List ops result in sequential scans on the table, instead of using the index set on the parent_path column of the vault_kv_store table.

What I expect

Vault List operations, with the PostgreSQL physical backend, should use the existing vault_kv_store indexes, and avoid sequential scans.

Investigation

The List query as defined in https://github.com/hashicorp/vault/blob/v0.7.3/physical/postgresql.go#L73-L75, uses the concat function, resulting on sequential scans:

enclave=> EXPLAIN SELECT DISTINCT substring(substr(path, length('test')+1) from '^.*?/') FROM "vault_kv_store" WHERE parent_path LIKE concat('test','%');
                                  QUERY PLAN
------------------------------------------------------------------------------
 Unique  (cost=2942.88..2942.89 rows=1 width=32)
   ->  Sort  (cost=2942.88..2942.89 rows=1 width=32)
         Sort Key: ("substring"(substr(path, 5), '^.*?/'::text)) COLLATE "C"
         ->  Seq Scan on vault_kv_store  (cost=0.00..2942.87 rows=1 width=32)
               Filter: (parent_path ~~ concat('test', '%'))
(5 rows)

Using the || string concatenation operator fixes the issue.

enclave=> EXPLAIN SELECT DISTINCT substring(substr(path, length('test')+1) from '^.*?/') FROM "vault_kv_store" WHERE parent_path LIKE 'test' || '%';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Unique  (cost=8.32..8.32 rows=1 width=32)
   ->  Sort  (cost=8.32..8.32 rows=1 width=32)
         Sort Key: ("substring"(substr(path, 5), '^.*?/'::text)) COLLATE "C"
         ->  Index Scan using parent_path_idx on vault_kv_store  (cost=0.28..8.31 rows=1 width=32)
               Index Cond: ((parent_path >= 'test'::text) AND (parent_path < 'tesu'::text))
               Filter: (parent_path ~~ 'test%'::text)
(6 rows)

I believe this might be a regression introduced with #2393

Use `||` standard concatenation instead of the `concat` function in order to use the `vault_kv_store` index on `parent_path`.

jefferai · 2017-07-01T20:20:45Z

@ckarenz Any chance you can review this?

ckarenz

Nice find! This change looks good.

jefferai · 2017-07-02T22:56:11Z

Great, thanks!

[physical][postgresql] concat→|| operator

60731c7

Use `||` standard concatenation instead of the `concat` function in order to use the `vault_kv_store` index on `parent_path`.

jefferai added this to the 0.7.4 milestone Jul 1, 2017

jefferai changed the title ~~[physical][postgresql] improve list performance~~ Improve Postgres storage list performance Jul 1, 2017

ckarenz approved these changes Jul 2, 2017

View reviewed changes

jefferai merged commit ead553d into hashicorp:master Jul 2, 2017

jefferai modified the milestones: 0.7.4, 0.8.0 Jul 24, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Improve Postgres storage list performance #2945

Improve Postgres storage list performance #2945

yannmh commented Jun 29, 2017 •

edited

Loading

jefferai commented Jul 1, 2017

ckarenz left a comment •

edited

Loading

jefferai commented Jul 2, 2017

Improve Postgres storage list performance #2945

Improve Postgres storage list performance #2945

Conversation

yannmh commented Jun 29, 2017 • edited Loading

Summary

Issue

What I expect

Investigation

jefferai commented Jul 1, 2017

ckarenz left a comment • edited Loading

Choose a reason for hiding this comment

jefferai commented Jul 2, 2017

yannmh commented Jun 29, 2017 •

edited

Loading

ckarenz left a comment •

edited

Loading