List problems?

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

----- Forwarded message from Mischa Sandberg <mischa(at)ca(dot)sophos(dot)com> -----

X-Spam-Checker-Version: SpamAssassin 3.1.0 (2005-09-13) on noel.decibel.org
X-Spam-Level:
X-Spam-Status: No, score=-2.4 required=5.0 tests=BAYES_00,DNS_FROM_RFC_ABUSE
autolearn=no version=3.1.0
X-Greylist: from auto-whitelisted by SQLgrey-
Date: Mon, 22 May 2006 14:51:56 -0700
From: Mischa Sandberg <mischa(at)ca(dot)sophos(dot)com>
Reply-To: mischa(at)ca(dot)sophos(dot)com
User-Agent: Thunderbird 1.5.0.2 (X11/20060420)
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Porting MSSQL to PGSQL: trace and profile
In-Reply-To: <20060522150647(dot)GE24404(at)svana(dot)org>
X-Mailing-List: pgsql-hackers
Precedence: bulk

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 jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461