Message-ID: <461AB6CF.3060202@modgraph-usa.com>
Date: Mon, 09 Apr 2007 14:57:35 -0700
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Thunderbird 1.5.0.10 (X11/20070221)
MIME-Version: 1.0
To: "Spiegelberg, Greg" <gspiegelberg@cranel.com>
CC: pgsql-performance@postgresql.org
Subject: Re: DELETE with filter on ctid
References: <82E74D266CB9B44390D3CCE44A781ED90B6ADC@POSTOFFICE.cranel.local>
In-Reply-To: <82E74D266CB9B44390D3CCE44A781ED90B6ADC@POSTOFFICE.cranel.local>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Spiegelberg, Greg wrote:
> We have a query which generates a small set of rows (~1,000) which are 
> to be used in a DELETE on the same table.  The problem we have is that 
> we need to join on 5 different columns and it takes far too long.

You may have encountered the same problem I did:  You *must* run ANALYZE on a temporary table before you use in another query.  It's surprising that this is true even for very small tables (a few hundred to a few thousand rows), but it is.  I had a case where I created a "scratch" table like yours, and the before/after ANALYZE performance was the difference between 30 seconds and a few milliseconds for the same query.

Craig