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

[Richard Sydney-Smith]

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.