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.
