From: Amit Kapila <amit.kapila@huawei.com>
To: fburgess@radiantblue.com
To: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
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> (raw)
In-Reply-To: <20130521145345.5a830134ae84016b0174832fdc1a3173.0082853132.wbe@email11.secureserver.net>
References: <20130521145345.5a830134ae84016b0174832fdc1a3173.0082853132.wbe@email11.secureserver.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 PRIMARY KEY (id )
> serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1_.ALGORITHM='SMAT';
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=4213952.17..4213952.18 rows=1 width=0)
> -> Hash Join (cost=230573.06..4213943.93 rows=3296 width=0)
> Hash Cond: (this_.SARS_RUN_ID=tr1_.ID)
> -> Seq Scan om sars_acts this_ (cost=0.00..3844241.84 rows=37092284 width=8)
> -> Hash (cost=230565.81..230565.81 rows=580 width=8)
> -> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)
> Filter: ((algorithm)::text = 'SMAT'::text)
> (7 rows)
> This query executes in approximately 5.3 minutes to complete, very very slow, our users are not happy.
> I did add an index on SARS_ACTS_RUN.ALGORITHM column but it didn't improve the run time.
> The planner just changed 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.
> -> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)
> Filter: ((algorithm)::text = '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 to check which plan it uses:
Analyze SARS_ACTS;
Analyze SARS_ACTS_RUN;
With Regards,
Amit Kapila.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: amit.kapila@huawei.com, fburgess@radiantblue.com, pgsql-general@postgresql.org
Subject: Re: Very slow inner join query Unacceptable latency.
In-Reply-To: <007501ce56b1$397ce690$ac76b3b0$@kapila@huawei.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox