Re: [PERFORM] query produces 1 GB temp file

2005-02-06 Thread John A Meinel
Dirk Lutzebaeck wrote: Greg, Thanks for your analysis. But I dont get any better after bumping STATISTICS target from 10 to 200. explain analyze shows that the optimizer is still way off estimating the rows. Is this normal? It still produces a 1 GB temp file. I simplified the query a bit, now

[PERFORM] Are JOINs allowed with DELETE FROM

2005-02-06 Thread Steven Rosenstein
This is probably a very trivial question and I feel foolish in even posting it, but I cannot seem to get it to work. SCENARIO (abstracted): Two tables, summary and detail. The schema of summary looks like: id int serial sequential record id collect_date date date the

Re: [PERFORM] Are JOINs allowed with DELETE FROM

2005-02-06 Thread Gaetano Mendola
Steven Rosenstein wrote: DELETE FROM detail JOIN summary ON (summary.id=detail.sum_id) WHERE collect_date='2005-02-05'; DELETE FROM detail WHERE detail.sum_id in ( select id from summary ) AND collect_date='2005-02-05'; Regards Gaetano Mendola ---(end of

Re: [PERFORM] Are JOINs allowed with DELETE FROM

2005-02-06 Thread Michael Fuhr
On Sun, Feb 06, 2005 at 12:16:13PM -0500, Steven Rosenstein wrote: DELETE FROM detail JOIN summary ON (summary.id=detail.sum_id) WHERE collect_date='2005-02-05'; But I keep getting a parser error. Am I not allowed to use JOINs in a DELETE statement, or am I just fat-fingering the SQL text

Re: [PERFORM] Are JOINs allowed with DELETE FROM

2005-02-06 Thread John Arbash Meinel
Gaetano Mendola wrote: Steven Rosenstein wrote: DELETE FROM detail JOIN summary ON (summary.id=detail.sum_id) WHERE collect_date='2005-02-05'; You have to tell it what table you are deleting from. Select * from A join B is both tables. What you want to do is fix the where clause. DELETE FROM

Re: [PERFORM] Are JOINs allowed with DELETE FROM

2005-02-06 Thread Steven Rosenstein
Hi Michael, Thank you for the link to the documentation page. I forgot to mention that we're still using version 7.3. When I checked the 7.3 documentation for DELETE, there was no mention of being able to use fields from different tables in a WHERE clause. This feature must have been added

Re: [PERFORM] Are JOINs allowed with DELETE FROM

2005-02-06 Thread Tom Lane
Steven Rosenstein [EMAIL PROTECTED] writes: Thank you for the link to the documentation page. I forgot to mention that we're still using version 7.3. When I checked the 7.3 documentation for DELETE, there was no mention of being able to use fields from different tables in a WHERE clause.

[PERFORM] Can the V7.3 EXPLAIN ANALYZE be trusted?

2005-02-06 Thread Steven Rosenstein
While working on a previous question I posed to this group, I ran a number of EXPLAIN ANALYZE's to provide as examples. After sending up my last email, I ran the same query *without* EXPLAIN ANALYZE. The runtimes were vastly different. In the following example, I ran two identical queries

Re: [PERFORM] Are JOINs allowed with DELETE FROM

2005-02-06 Thread Steven Rosenstein
Many thanks to Gaetano Mendola and Tom Lane for the hints about using fields from other tables in a DELETE's WHERE clause. That was the magic bullet I needed, and my application is working as expected. --- Steve

Re: [PERFORM] Can the V7.3 EXPLAIN ANALYZE be trusted?

2005-02-06 Thread Leeuw van der, Tim
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Steven Rosenstein Sent: Sunday, February 06, 2005 8:51 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Can the V7.3 EXPLAIN ANALYZE be trusted? While working on a previous question I posed to

Re: [PERFORM] Can the V7.3 EXPLAIN ANALYZE be trusted?

2005-02-06 Thread Greg Stark
Leeuw van der, Tim [EMAIL PROTECTED] writes: I don't think EXPLAIN ANALYZE puts that much overhead on a query. EXPLAIN ANALYZE does indeed impose a significant overhead. What percentage of the time is overhead depends heavily on how much i/o the query is doing. For queries that are primarily

Re: [PERFORM] Can the V7.3 EXPLAIN ANALYZE be trusted?

2005-02-06 Thread Tom Lane
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Steven Rosenstein I don't think EXPLAIN ANALYZE puts that much overhead on a query. I think you're being overly optimistic. The explain shows that the Materialize subnode is being entered upwards of 32 million times: -

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-02-06 Thread Jan Wieck
On 1/28/2005 2:49 PM, Christopher Browne wrote: But there's nothing wrong with the idea of using pg_dump --data-only against a subscriber node to get you the data without putting a load on the origin. And then pulling the schema from the origin, which oughtn't be terribly expensive there. And

Re: [PERFORM] Can the V7.3 EXPLAIN ANALYZE be trusted?

2005-02-06 Thread Steven Rosenstein
You're probably right about my being overly optimistic about the load imposed by EXPLAIN ANALYZE. It was just that in my previous experience with it, I'd never seen such a large runtime discrepancy before. I even allowed for a caching effect by making sure the server was all but quiescent,