Hello hackers,
This email is regarding the Postgres pg_stat_statements extension.
I noticed that enabling pg_stat_statements can effect performance. I thought
that changing the pg_stat_statements.track parameter to 'none' could reduce
this overhead without requiring a restart to remove it from
shared_preload_libraries. Changing this config did not improve performance as I
expected. Looking over the code, I noticed that pg_stat_statements is not
checking if it is enabled before executing the post_parse_analyze_hook
function. Other hooks that require access to the pg_stat_statements query hash
table (through the pgss_store function) check for pgss_enabled.
Would it make sense to check for pgss_enabled in the post_parse_analyze_hook
function?
**Patching**
Making this change drastically improved performance while
pg_stat_statement.track was set to NONE. This change allows me to more
effectively enable/disable pg_stat_statements without requiring a restart.
Example patch:
@@ -783,8 +783,8 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
/* Assert we didn't do this already */
Assert(query->queryId == 0);
- /* Safety check... */
- if (!pgss || !pgss_hash)
+ /* Safety check...and ensure that pgss is enabled before we do any work
*/
+ if (!pgss || !pgss_hash || !pgss_enabled())
return;
**Simple Mini Benchmark**
I ran a simple test on my local machine with this spec: 16 core/32 GB
memory/Windows Server 2016.
The simple query I used was 'select 1'. I called pg_stat_statements_reset()
before each simple query to clear the pg_stat_statements query hash. The
majority of the latency happens the first time a query is run.
Median runtime of 100 simple queries in milliseconds:
PGSS loaded (ms) PGSS loaded + this patch (ms)
track = top 0.53 0.55
track = none 0.41 0.20
PGSS not loaded: 0.18ms
--
Raymond Martin
[email protected]
Azure Database for PostgreSQL
