Postgres Pro
Downloads
Home   >   mailing lists

Thread: Very slow inner join query unacceptable latency

Very slow inner join query unacceptable latency

From
Date:
<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">The SARS_ACTS table currently has 37,115,515 rowswe have =
indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree (sars_run_id)<b=
r>we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIM=
ARY KEY (id )serverdb=3D# explain select count(*) as y0_ from SARS_=
ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=3D<a href=3D"=
http://tr1_.ID">tr1_.ID where tr1_.ALGORITHM=3D'SMAT';  &=
nbsp;           &nbs=
p;            &=
nbsp;           &nbs=
p;            QUERY =
PLAN-------------------------------------------------------------------=
-------------------------------------------------------Aggregate  =
(cost=3D4213952.17..4213952.18 rows=3D1 width=3D0)  -> Hash Joi=
n  (cost=3D230573.06..4213943.93 rows=3D3296 width=3D0)  =
;     Hash Cond:  (this_.SARS_RUN_ID=3D<a href=3D"=
http://tr1_.ID">tr1_.ID)       -> =
 Seq Scan om sars_acts this_  (cost=3D0.00..3844241.84 rows=3D37092284=
 width=3D8)       ->  Hash  (cost=
=3D230565.81..230565.81 rows=3D580 width=3D8)     &=
nbsp;        -> Seq Scan on sars_acts=
_run tr1_  (cost=3D0.00..230565.81 rows=3D580 width=3D8) &nbs=
p;              &nbs=
p;  Filter:  ((algorithm)::text =3D 'SMAT'::text)(7 rows)=
This query executes in approximately 5.3 minutes to complete, very very=
 slow, our users are not happy. Does anyone have =
suggestions about how to speed it up?thanks </d=
iv>

Re: Very slow inner join query unacceptable latency

From
Jaime Casanova
Date:
On Tue, May 21, 2013 at 3:54 PM,  <fburgess@radiantblue.com> wrote:
> The SARS_ACTS table currently has 37,115,515 rows
>
> we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree
> (sars_run_id)
> we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIM=
ARY
> KEY (id )
>
> serverdb=3D# explain select count(*) as y0_ from SARS_ACTS this_ inner jo=
in
> SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=3Dtr1_.ID where tr1_.ALGORITHM=3D=
'SMAT';

This is not a bug, you should write to the
pgsql-general@postgresql.org or pgsql-performance@postgresql.org
mailing lists.

anyway, seems that you need an additional index on SARS_ACTS_RUN.ALGORITHM

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
Phone: +593 4 5107566         Cell: +593 987171157

Re: Very slow inner join query unacceptable latency

From
Date:
<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