From: Amit Kapila <amit.kapila@huawei.com>
To: <fburgess@radiantblue.com>, <pgsql-performance@postgresql.org>
CC: <pgsql-general@postgresql.org>
References: 
 <20130521145345.5a830134ae84016b0174832fdc1a3173.0082853132.wbe@email11.secureserver.net>
In-Reply-To: 
 <20130521145345.5a830134ae84016b0174832fdc1a3173.0082853132.wbe@email11.secureserver.net>
Subject: Re: Very slow inner join query Unacceptable latency.
Date: Wed, 22 May 2013 11:27:19 +0530
Message-ID: <007501ce56b1$397ce690$ac76b3b0$@kapila@huawei.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Ac5WbdeqV33xQS+zSeShEyHX4BZmTgAQqlJQ
Content-Language: en-us
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Wednesday, May 22, 2013 3:24 AM fburgess 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';
>                                                    QUERY PLAN
> -------------------------------------------------------------------------=
-------------------------------------------------
> Aggregate  (cost=3D4213952.17..4213952.18 rows=3D1 width=3D0)
>  -> Hash Join  (cost=3D230573.06..4213943.93 rows=3D3296 width=3D0)
>       Hash Cond:  (this_.SARS_RUN_ID=3Dtr1_.ID)
>       ->  Seq Scan om sars_acts this_  (cost=3D0.00..3844241.84 rows=3D37=
092284 width=3D8)
>       ->  Hash  (cost=3D230565.81..230565.81 rows=3D580 width=3D8)
>              -> Seq Scan on sars_acts_run tr1_  (cost=3D0.00..230565.81 r=
ows=3D580 width=3D8)
>                   Filter:  ((algorithm)::text =3D 'SMAT'::text)
> (7 rows)


> This query executes in approximately 5.3 minutes to complete, very very s=
low, our users are not happy.

> I did add an index on SARS_ACTS_RUN.ALGORITHM column but it didn't improv=
e the run time.=20
> The planner just changed the "Filter:" to an "Index Scan:" improving the =
cost of the Seq Scan=20
> on the sars_acts_run table, but the overall run time remained the same. I=
t seems like the bottleneck=20
> is in the Seq Scan on the sars_acts table.

>              -> Seq Scan on sars_acts_run tr1_  (cost=3D0.00..230565.81 r=
ows=3D580 width=3D8)
>                   Filter:  ((algorithm)::text =3D 'SMAT'::text)

> Does anyone have suggestions about how to speed it up?

Could you please once trying Analyzing both tables and then run the query t=
o check which plan it uses:

Analyze SARS_ACTS;
Analyze SARS_ACTS_RUN;


With Regards,
Amit Kapila.


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance