From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Richard Sydney-Smith" <richard@ibisaustralia.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Slow sub-selects, max and count(*)
Date: Thu, 5 Feb 2004 19:59:43 -0800
User-Agent: KMail/1.5.4
References: <003b01c3ec5f$d740c3e0$76e98a90@athlon2000>
In-Reply-To: <003b01c3ec5f$d740c3e0$76e98a90@athlon2000>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200402051959.43263.josh@agliodbs.com>

Richard,

> Ian you suggested a simular problem was solved using "exists" rather than 
"in". I am not sure how the use differs. I have tried to include it in option 
4 below. 

Hmmm ... this piece of advice is dated; with PG 7.4, IN() queries should be 
plenty fast.  If you're using 7.3 or less, though watch out!


> 1) delete from fsechist where hist_q in (select hist_q from fsechist, 
temp_shareprices where hist_tick = ticker and dte = hist_date);

I can't help you with this unless you attribute column names to their tables.  
Please use full table.column syntax.


>  update fsechist set hist_tick = \'@@\' from fsechist h, temp_shareprices t 
where t.ticker = h.hist_tick and h.hist_date = t.dte;

Put an ANALYZE fsechist here.   
>  delete from fsechist where hist_tick = \'@@\';

Is there a reason why you are doing this in two steps?

> -- does not allow insert of a single company data
> delete from fsechist where hist_date in (select distinct dte from 
temp_shareprices);

The DISTINCT is completely unnecessary.

> delete from fsechist where exists(select 1 from fsechist, temp_shareprices 
where hist_tick = ticker and dte = hist_date);

This is not the same query as #1.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco