Re: Porting MSSQL to PGSQL: trace and profile

Martijn van Oosterhout wrote:

>>> For high-end MSSQL shops, a high value is being able to trace and profile
>>> (EXPLAIN) every client SQL command from the server side ... with plenty of
>>> options for selective trace.
>> This would also be highly valuable to have in PostgreSQL.
>
> Are we talking EXPLAIN (which is cheap) or EXPLAIN ANALYZE (which is
> less so)?

No, that's the thing; server-side trace and filtering are EXPLAIN ANALYZE, and
require no change to the app, catching (problem) plans in context.

For example, (using PG here) we had an ETL program that occasionally went very
stupid. It turned out that the problem was the interaction between autovacuum
timing, and when the program wiped out and rebuilt a worktable.
I had modified the app to EXPLAIN ANALYZE the update commands,
but how do you modify the straight SELECTs? Taking the statements out of context
and trying them offline with EXPLAIN ANALYZE sent me up the wrong tree
a few times :-(

With PG, where I end up is setting log_min_duration_statement=10
and grinding the logs for the information I want, hoping to convincingly replay
the SQL (with EXPLAIN ANALYZE) under similar circumstances.
If there are no applications that PREPARE their statements, I have a chance.
BTW was logging of (execution of) prepared statements with context info ever
considered? Or have I missed something?
----------
The MSSQL facility make on-the-fly EXPLAIN ANALYZE possible for all statements.
Its selective filter functions make feasible in production systems,
where you can turn it on for a week, to catch hard-to-reproduce issues.
----------
I did go digging into source to make EXPLAIN ANALYZE work for a server-side
trace, but it looked like a major. Any pointers? Willing to go dive into it again.
--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.