From: Josh Berkus <josh@agliodbs.com>
To: Richard Sydney-Smith <richard@ibisaustralia.com>
To: pgsql-sql@postgresql.org
Subject: Re: Slow sub-selects, max and count(*)
Date: Thu, 5 Feb 2004 19:59:43 -0800
Message-ID: <200402051959.43263.josh@agliodbs.com> (raw)
In-Reply-To: <003b01c3ec5f$d740c3e0$76e98a90@athlon2000>
References: <003b01c3ec5f$d740c3e0$76e98a90@athlon2000>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, richard@ibisaustralia.com
Subject: Re: Slow sub-selects, max and count(*)
In-Reply-To: <200402051959.43263.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox