X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F2C53D1B917 for ; Thu, 5 Feb 2004 01:46:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 50725-02 for ; Wed, 4 Feb 2004 21:46:13 -0400 (AST) Received: from server146.hostingrevolution.com (server146.hostingrevolution.com [64.21.143.245]) by svr1.postgresql.org (Postfix) with ESMTP id DB4DCD1B45B for ; Wed, 4 Feb 2004 21:46:10 -0400 (AST) Received: from wtpp-p-144-138-221-18.prem.tmns.net.au ([144.138.221.18] helo=athlon2000) by server146.hostingrevolution.com with smtp (Exim 4.24) id 1AoYam-0005uU-LS for pgsql-sql@postgresql.org; Wed, 04 Feb 2004 19:45:47 -0600 Message-ID: <001001c3eb8a$255d6dc0$5801a8c0@athlon2000> From: "Richard Sydney-Smith" To: Subject: Slow sub-selects, max and count(*) Date: Thu, 5 Feb 2004 09:48:21 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000D_01C3EBCD.30FA9F20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - server146.hostingrevolution.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - ibisaustralia.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/49 X-Sequence-Number: 16740 This is a multi-part message in MIME format. ------=_NextPart_000_000D_01C3EBCD.30FA9F20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a procedure ( included below with table definition) to import end of= day quotes into a table fsechist. The issue is with the speed ( or lackof it) that the procedure proceeds. Ap= parently it is the subselects that are the worst issue and I have tried to = replace these. Also max() and count() refuse to use indexes. I have replace= d max() with (select ... as mx from ... order by ... desc limit 1) and this= is much faster but appear to have to avoid count() as I can not find a way= to point this at an index. Please, I am sure that there is a better way to do this. And 5 to 7 minutes= to insert a day is really too slow. Each days import table contains about = 3200 records. Total table size for fsechist is about 2.5 million records. If you could point me in the right direction here it would be much apprecia= ted. thanks Richard Sydney-Smith ---------------------------------------------------------------------------= ----------------------------- CREATE OR REPLACE FUNCTION public.import_sharedata(varchar) RETURNS bool AS ' declare filnam alias for $1; alldone bool :=3D true; /* cnt integer :=3D 0; */ /* count of number inserted not used */ begin /* Imports a text file containing end of day price quotes filnam should be in double backslash format e.g. c:\\data\\sharequotes\\= quotes.dat =20=20 Ensures that it only brings in quotes for companies we have listed in ou= r database and ensures only the latest copy of the quote is kept for each day Import table has 7 columns in CSV tab delimited ascii format. EzyChart format has prices in cents,=20=20 METASTOCK in dollars MetaStock +100 has the volume divided by 100. =20=20=20 Depending on your import data source you may need to process the temp_sh= areprices so that the=20 values are in Dollars , not cents and the volume is per unit not per 100= units. Present calculation is for EzyChart format. Both tables have to have a 4 digit year yyyymmdd =20=20 Warning : If you have the wrong format this procedure will import garbag= e into your database !!! */ perform drop_table(\'temp_shareprices\'); =20=20 create table temp_shareprices( ticker char(10) null, dte char(8), v1 numeric(19,4) not null default 0, v2 numeric(19,4) not null default 0, v3 numeric(19,4) not null default 0, v4 numeric(19,4) not null default 0, v5 numeric(19,4) not null default 0 ); execute \'copy temp_shareprices from \'||quote_literal(filnam)||\' delimi= ter \'||quote_literal(\',\'); -- extend yr to 4 digits, alter quotes to dollars update temp_shareprices set dte =3D \'20\'||dte,v1=3Dv1/100,v2=3Dv2/100,v= 3=3Dv3/100,v4=3Dv4/100; -- add in any new company tickers insert into fsecurity (sec_cde) select ticker from temp_shareprices where l= ength(trim(ticker))=3D3 and ticker not in ( select sec_cde from fsecurity); -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- remove rows from temp_shareprices where we do not have a corresponding s= ecurity -- using sub-select is slow -- delete from temp_shareprices where ticker not in (select sec_cde from fs= ecurity); -- can just delete ones where length<>3 as insert above ensures all length = 3 are inserted. All ASX codes are 3char long delete from temp_shareprices where length(trim(ticker))<>3; -- Attempt to remove without limiting to length and not using sub select do= es not work --update temp_shareprices set ticker =3D s.sec_cde from temp_shareprices t = left outer join fsecurity s on t.ticker =3D s.sec_cde; --delete from temp_shareprices where ticker is null; -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 -- put indexes on temp table CREATE INDEX temphist_tick ON temp_shareprices USING btree (ticker); CREATE INDEX temphist_dte ON temp_shareprices USING btree (dte); -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 -- Scan the input table and delete as appropriate from fsechist -- Using sub select to remove any pre-existing records in fsechist very slow -- delete from fsechist where hist_q in (select hist_q from fsechist, temp_= shareprices where hist_tick =3D ticker and dte =3D hist_date); -- Tried to replace subselect=20 update fsechist set hist_tick =3D \'@@\' from fsechist h, temp_shareprices = t where t.ticker =3D h.hist_tick and h.hist_date =3D t.dte; delete from fsechist where hist_tick =3D \'@@\'; -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 -- insert newvalues=20 insert into fsechist(hist_tick,hist_date,hist_v1,hist_v2,hist_v3,hist_v4,hi= st_vol) select ticker,dte,v1,v2,v3,v4,v5 from temp_shareprices; -- count the number inserted -- select count(*) into cnt from temp_shareprices; -- perform drop_table(\'temp_shareprices\'); return alldone; end;' LANGUAGE 'plpgsql' VOLATILE; Note : drop_table is a simple procedure that drops a procedure by name iff = it exists. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Definition of Fsechist CREATE TABLE public.fsechist ( hist_q int4 NOT NULL DEFAULT nextval('hist_q'::text), hist_v1 numeric(19,4), hist_v2 numeric(19,4), hist_v3 numeric(19,4), hist_v4 numeric(19,4), hist_vol numeric(19,4), hist_deltar numeric(19,4), hist_deltag numeric(19,4), hist_date char(8), hist_tick char(10), hist_announce char(1), CONSTRAINT fsechist_pkey PRIMARY KEY (hist_q) ) WITH OIDS; CREATE INDEX fsechist_date ON public.fsechist USING btree (hist_date); CREATE UNIQUE INDEX fsechist_q ON public.fsechist USING btree (hist_q); CREATE INDEX fsechist_tick ON public.fsechist USING btree (hist_tick); ------=_NextPart_000_000D_01C3EBCD.30FA9F20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have a procedure ( included below w= ith table=20 definition) to import end of day quotes into a table fsechist.
 
The issue is with the speed ( or lackof it= ) that=20 the procedure proceeds. Apparently it is the subselects that are the worst = issue=20 and I have tried to replace these. Also max() and count() refuse to use ind= exes.=20 I have replaced max() with (select ... as mx from ... order by ... desc lim= it 1)=20 and this is much faster but appear to have to avoid count() as I can not fi= nd a=20 way to point this at an index.
 
Please, I am sure that there is a better w= ay to do=20 this. And 5 to 7 minutes to insert a day is really too slow. Each days impo= rt=20 table contains about 3200 records. Total table size for fsechist is about 2= .5=20 million records.
 
 
If you could point me in the right directi= on here=20 it would be much appreciated.
 
thanks
 
Richard Sydney-Smith
 
 
------------------------------------------------------------------= --------------------------------------
 
CREATE OR REPLACE FUNCTION=20 public.import_sharedata(varchar)
  RETURNS bool AS
'
 
declare
   filnam alias for= =20 $1;
   alldone bool :=3D true;
   /* cnt integer = :=3D 0;=20 */  /* count of number inserted not used */
 
begin
/*
   Imports a text= file=20 containing end of day price quotes
   filnam should be in doub= le=20 backslash format e.g. c:\\data\\sharequotes\\quotes.dat
 =20
   Ensures that it only brings in quotes for companies we hav= e=20 listed in our database
   and ensures only the latest copy of = the=20 quote is kept for each day
 
   Import table has 7 columns in= CSV tab=20 delimited ascii format.
      EzyChart format h= as=20 prices in cents, 
      METASTOCK in=20 dollars
      MetaStock +100 has the volume div= ided=20 by 100.
  
   Depending on your import data sour= ce=20 you may need to process the temp_shareprices so that the
   v= alues=20 are in Dollars , not cents and the volume is per unit not per 100=20 units.
 
   Present calculation is for Ez= yChart=20 format.
 
   Both tables  have to hav= e a 4=20 digit year yyyymmdd
 
   Warning : If you have the wr= ong=20 format this procedure will import garbage into your database !!!
 
*/
 
  perform=20 drop_table(\'temp_shareprices\');
 
  create table=20 temp_shareprices(
    ticker char(10)=20 null,
    dte char(8),
    v1 numeric(1= 9,4)=20 not null default 0,
    v2 numeric(19,4) not null default= =20 0,
    v3 numeric(19,4) not null default=20 0,
    v4 numeric(19,4) not null default=20 0,
    v5 numeric(19,4) not null default=20 0
    );
  execute \'copy temp_shareprices from= =20 \'||quote_literal(filnam)||\' delimiter \'||quote_literal(\',\');
 

-- extend yr to 4 digits, alter quotes to dollars
 
  update temp_shareprices set dte =3D=20 \'20\'||dte,v1=3Dv1/100,v2=3Dv2/100,v3=3Dv3/100,v4=3Dv4/100;
 
-- add in any new company tickers
insert into fsecurity (sec_cde) s= elect=20 ticker from temp_shareprices where length(trim(ticker))=3D3 and ticker not = in (=20 select sec_cde from fsecurity);
 
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
-- remove rows from temp_shareprices where we do not have a correspond= ing=20 security
-- using sub-select is slow
-- delete from temp_shareprices where t= icker=20 not in (select sec_cde from fsecurity);
-- can just delete ones where length<>3 as insert above ensures = all=20 length 3 are inserted. All ASX codes are 3char long
delete from=20 temp_shareprices where length(trim(ticker))<>3;
 
-- Attempt to remove without limiting to length and not using sub sele= ct=20 does not work
--update temp_shareprices set ticker =3D s.sec_cde from=20 temp_shareprices t left outer join fsecurity s on t.ticker =3D=20 s.sec_cde;
--delete from temp_shareprices where ticker is null;
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D <= /DIV>

-- put indexes on temp table
CREATE INDEX temphist_tick ON=20 temp_shareprices  USING btree  (ticker);
CREATE INDEX=20 temphist_dte  ON temp_shareprices  USING btree  (dte);
 
 
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D <= /DIV>-- Scan the input table and delete=20 as appropriate from fsechist
 
-- Using sub select to remove any pre-existing records in fsechist ver= y=20 slow
-- delete from fsechist where hist_q in (select hist_q from fsechis= t,=20 temp_shareprices where hist_tick =3D ticker and dte =3D hist_date);
 
-- Tried to replace subselect
update fsechist set hist_tick =3D \'= @@\'=20 from fsechist h, temp_shareprices t where t.ticker =3D h.hist_tick and h.hi= st_date=20 =3D t.dte;
delete from fsechist where hist_tick =3D \'@@\';
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D <= /DIV>
 
-- insert newvalues
insert into=20 fsechist(hist_tick,hist_date,hist_v1,hist_v2,hist_v3,hist_v4,hist_vol)=20 select   ticker,dte,v1,v2,v3,v4,v5 from temp_shareprices;
 
-- count the number inserted
-- select   count(*) into cn= t=20 from temp_shareprices;
 
-- perform  drop_table(\'temp_shareprices\');
 
return alldone;
 
end;'
  LANGUAGE 'plpgsql' VOLATILE;
 
 
Note : drop_table is a simple procedure th= at drops=20 a procedure by name iff it exists.
 
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Definition of Fsechist
 
CREATE TABLE public.fsechist
(
 = ; hist_q=20 int4 NOT NULL DEFAULT nextval('hist_q'::text),
  hist_v1=20 numeric(19,4),
  hist_v2 numeric(19,4),
  hist_v3=20 numeric(19,4),
  hist_v4 numeric(19,4),
  hist_vol=20 numeric(19,4),
  hist_deltar numeric(19,4),
  hist_deltag= =20 numeric(19,4),
  hist_date char(8),
  hist_tick=20 char(10),
  hist_announce char(1),
  CONSTRAINT fsechist_pk= ey=20 PRIMARY KEY (hist_q)
) WITH OIDS;
 
CREATE INDEX fsechist_date  ON=20 public.fsechist  USING btree  (hist_date);
 
CREATE UNIQUE INDEX fsechist_q  ON=20 public.fsechist  USING btree  (hist_q);
CREATE INDEX fsechist_tick  ON=20 public.fsechist  USING btree  (hist_tick);

 ------=_NextPart_000_000D_01C3EBCD.30FA9F20--