Subject: List problems?

From: "Jim C. Nasby" <[email-masked]>
Date: Tue, 23 May 2006 18:37:39 -0500

Is the list having issues? I'd swear that I've seen this (and other)
emails multiple times now...

----- Forwarded message from Mischa Sandberg <[email-masked]> -----

Date: Mon, 22 May 2006 14:51:56 -0700
From: Mischa Sandberg <[email-masked]>
Reply-To: [email-masked]
To: [email-masked]
Subject: Re: [HACKERS] 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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

----- End forwarded message -----

-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Tom Lane <[email-masked]>
Date: Tue, 23 May 2006 23:00:47 -0400

> emails multiple times now...

No dups noticed here --- please pay attention to your Received: lines
and see if you can determine where the dups are originating in your
mail path.

			regards, tom lane