Re: Slow sub-selects, max and count(*)

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.