From: Richard Sydney-Smith <richard@ibisaustralia.com>
To: pgsql-sql@postgresql.org
Subject: Re: Slow sub-selects, max and count(*)
Date: Thu, 5 Feb 2004 15:53:08 +0800
Message-ID: <003b01c3ec5f$d740c3e0$76e98a90@athlon2000> (raw)
Thanks Josh and Ian,
narrowing the problem down. The really slow line is the one where I try and remove potential duplicates. It does not look at the indexes.
Point on using copy rather than insert is taken. I use copy to load to a temp file so I can test the data and alter certain values. I suppose I could: copy to temp / alter/ save/ copy into fsechist but that is rather ugly and as it is not the insert that is the real bug bear I would like to fix this first.
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.
Trial Solutions:
1) delete from fsechist where hist_q in (select hist_q from fsechist, temp_shareprices where hist_tick = ticker and dte = hist_date);
far too slow
2)
update fsechist set hist_tick = \'@@\' from fsechist h, temp_shareprices t where t.ticker = h.hist_tick and h.hist_date = t.dte;
delete from fsechist where hist_tick = \'@@\';
really , really slow. Thought this would use the indexes but "explain" ( being my friend) still gives the query plan as a sequential scan
3)
-- does not allow insert of a single company data
delete from fsechist where hist_date in (select distinct dte from temp_shareprices);
Works quick enough but has limited functionality. ( does not cope with imports other than a full end of trading)
4)
delete from fsechist where exists(select 1 from fsechist, temp_shareprices where hist_tick = ticker and dte = hist_date);
Tried and still took over 60 secs before I cancelled the request.
Indexes
Both tables have indexes defined for tick and date. tick and date of the same data type in both cases.
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: richard@ibisaustralia.com
Subject: Re: Slow sub-selects, max and count(*)
In-Reply-To: <003b01c3ec5f$d740c3e0$76e98a90@athlon2000>
* 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