Appears to fail with pg-stat-statements
GeekOnCoffee opened this issue · 4 comments
Setup Logs:
SQL: SELECT * FROM pg_available_extensions WHERE name = 'hypopg' AND installed_version IS NOT NULL
SQL: SET lock_timeout = '5s'
SQL: SELECT DISTINCT query FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid WHERE datname = current_database() AND total_time >= 0 AND calls >= 0 ORDER BY 1
Processing 376 new query fingerprints
SQL: SELECT hypopg_reset()
SQL: SELECT table_schema || '.' || table_name AS table_name FROM information_schema.tables WHERE table_catalog = current_database()
SQL: SELECT current_schemas(true)
Seeing a large collection of these:
--------------------------------------------------------------------------------
Query 0203bca7f31aec4f9d9c0461948d15c9c70d5cf34d
Could not run explain
SELECT $3 AS one FROM "clinics" WHERE "clinics"."email" = $1 LIMIT $2
--------------------------------------------------------------------------------
When I look at the queries being run afcb722#diff-0b08b6751f5dc6621f1755f87993ee89R299 it appears that it's getting the query with the placeholders. It doesn't look like there's anything in that table containing them.
Is there some configuration to pg-stat-statements I need? I didn't have any luck getting the logging working, which is why I went this route.
Happy to help debug this and even push a fix, but not really sure how to start on the postgres side!
Hey @GeekOnCoffee, as far as I know, there's no way to get pg_stat_statements to store the placeholder values, so Dexter can't optimize queries that have them. For this reason, logs are a better option. What was the trouble with logs?
Gotcha. I'll revisit getting logs working. Probably would be worth adding a note about that in the pg_stat_statements section, as if you're using Rails (or likely other things like it), this won't work. It'd also be nice seeing some sort of output without the debugging indicating that it wasn't able to do anything with x queries, if I didn't know to look at the debugging, I would have thought I was good.
Added a note to the readme about pg_stat_statements. I'm a bit hesitant to add output about not being able to process certain queries by default. This will happen even when Dexter is running fine (for instance set queries aren't explainable) and think it may confuse people.
👍 I did get logs outputting, I haven't yet gotten suggestion improvements, but I haven't had time to investigate that, so it might be operator error.
Long term, it might make sense to break those into a few categories:
Total Queries
Total Processable Queries (Excluding set, etc which we know can't be explained)
Total Queries Processed
This can be closed, and I'll open a new one if I have additional issues!