Re: Very slow inner join query unacceptable latency

<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">Thanks Jaime for your feedback, I did add an index on SARS_ACTS_R=
UN.ALGORITHM column but it didn't improve the run time. The planner just ch=
anged the "Filter:" to an "Index Scan:" improving the cost of the Seq Scan =
on the =
sars_acts_run table, but the overall run time remained the same. It =
seems like the bottleneck is in the Seq Scan on the sars_acts table.</d=
iv><span style=3D"font-family:Verdana; color:#000000; f=
ont-size:10pt;">          &nbs=
p;   -> Seq Scan on sars_acts_run tr1_  (cost=3D0.00..230=
565.81 rows=3D580 width=3D8)         =
;          Filter:  ((algorith=
m)::text =3D 'SMAT'::text)<span style=3D"font-family:Verd=
ana; color:#000000; font-size:10pt;"><span style=3D"f=
ont-family:Verdana; color:#000000; font-size:10pt;">I'll move this posting =
into the appropriate group<span style=3D"font-family:Verd=
ana; color:#000000; font-size:10pt;"><span style=3D"f=
ont-family:Verdana; color:#000000; font-size:10pt;">thanks<div=
></=
span>=0A<blockquote id=3D"replyBlockquote" webmail=3D"1" style=3D"bor=
der-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10=
pt; color:black; font-family:verdana;">=0A=0A---=
----- Original Message --------=0ASubject: Re: [BUGS] Very slow inner j=
oin query unacceptable latency=0AFrom: Jaime Casanova <<a href=3D"ma=
ilto:jaime@2ndquadrant.com">jaime@2ndquadrant.com>=0ADate: Tue, =
May 21, 2013 2:34 pm=0ATo: Freddie Burgess <<a href=3D"mailto:fburge=
ss@radiantblue.com">fburgess@radiantblue.com>=0ACc: pgsql-bugs &=
lt;pgsql-bugs@postgresql.org</=
a>>=0A=0AOn Tue, May 21, 2013 at 3:54 PM,  <<a href=3D"mailto=
:fburgess@radiantblue.com">fburgess@radiantblue.com> wrote:=0A&g=
t; The SARS_ACTS table currently has 37,115,515 rows=0A>=0A> =
we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree=0A&=
gt; (sars_run_id)=0A> we have pk constraint on the SARS_ACTS_RUN tab=
le; sars_acts_run_pkey PRIMARY=0A> KEY (id )=0A>=0A> s=
erverdb=3D# explain select count(*) as y0_ from SARS_ACTS this_ inner join<=
br>=0A> SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=3D<a href=3D"http://tr1_=
.ID">tr1_.ID where tr1_.ALGORITHM=3D'SMAT';=0A=0AThis is not a =
bug, you should write to the=0A<a href=3D"mailto:pgsql-general@postgres=
ql.org">pgsql-general@postgresql.org or <a href=3D"mailto:pgsql-perform=
ance@postgresql.org">pgsql-performance@postgresql.org=0Amailing lis=
ts.=0A=0Aanyway, seems that you need an additional index on SARS_AC=
TS_RUN.ALGORITHM=0A=0A--=0AJaime Casanova         <a href=3D"ht=
tp://www.2ndQuadrant.com">www.2ndQuadrant.com=0AProfessional Postgr=
eSQL: Soporte 24x7 y capacitaci=C3=B3n=0APhone: +593 4 5107566         =
Cell: +593 987171157=0A=0A=0A-- =0ASent via pgsql-bugs mail=
ing list (pgsql-bugs@postgresq=
l.org)=0ATo make changes to your subscription:=0A<a href=3D"htt=
p://www.postgresql.org/mailpref/pgsql-bugs">http://www.postgresql.org/mailp=
ref/pgsql-bugs=0A=0A=0A