Thanks Jaime for your feedback, 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)
Filter: ((algorithm)::text = 'SMAT'::text)
I'll move this posting into the appropriate group
thanks
-------- Original Message --------
Subject: Re: [BUGS] Very slow inner join query unacceptable latency
From: Jaime Casanova <ja...@2ndquadrant.com>
Date: Tue, May 21, 2013 2:34 pm
To: Freddie Burgess <fburg...@radiantblue.com>
Cc: pgsql-bugs <pgsql-bugs@postgresql.org>
On Tue, May 21, 2013 at 3:54 PM, <fburg...@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 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';
This is not a bug, you should write to the
pgsql-gene...@postgresql.org or pgsql-performa...@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ón
Phone: +593 4 5107566 Cell: +593 987171157
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
