Re: Slow sub-selects, max and count(*)
От | Bruno Wolff III |
---|---|
Тема | Re: Slow sub-selects, max and count(*) |
Дата | |
Msg-id | 20040206033941.GA16374@wolff.to обсуждение исходный текст |
Ответ на | Re: Slow sub-selects, max and count(*) ("Richard Sydney-Smith" <richard@ibisaustralia.com>) |
Список | pgsql-sql |
On Thu, Feb 05, 2004 at 15:53:08 +0800, Richard Sydney-Smith <richard@ibisaustralia.com> wrote: > 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); Don't you want: delete from fsechist where hist_q in (select hist_q from temp_shareprices where hist_tick = ticker and dte = hist_date); You should be able to refer to the table being deleted instead of joining to it again in the subselect. As long as you are using 7.4.x, this should be pretty fast. > > far too slow Since this is slower than the exists solutions, my guess is that you are using a version prior to 7.4. > > 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 asa sequential scan Again don't add fsechist to the from list. This joins with fsechist twice. > > 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) I think using the distinct clause is going to slow this down, especially if you are using a version prior to 7.4 since a sort is used instead of a hash table to get unique values. > > 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 Again, drop fsechist from the exists subselect. > > Both tables have indexes defined for tick and date. tick and date of the same data type in both cases. You can also do a join using the nonstandard implied from syntax. You can do something like: delete from fsechist where hist_tick = temp_shareprices.ticker and temp_shareprices.dte = hist_date;
В списке pgsql-sql по дате отправления: