Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

We re-tested these settings a few times after our initial test and realized that the execution time I posted was shewed, because the execution plan was cached after the initial run. Subsequent executions ran in a little over a second. There ended up being no significant saving by setting these para

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

On Fri, May 24, 2013 at 3:44 PM, wrote: > Total runtime: 1606.728 ms 1.6 seconds <- very good response time > improvement > > (7 rows) > > Questions: > > Any concerns with setting these conf variables you recommended; work_mem, > random_page_cost dbserver wide (in postgresql,conf)? > > Thanks so

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

me=0.007..2.659 rows=3360 loops=441)  Index Cond:  (SARS_RUN_ID=tr1_.ID)Total runtime: 1606.728 ms 1.6 seconds <- very good response time improvement(7 rows)Questions:Any concerns with setting these conf variables you recommended; work_mem, random_page_cost dbserver wide (in postgresql,conf

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

Looking at the execution plan makes me wonder what your work_mem is set to. Try cranking it up to test and lowering random_page_cost: set work_mem='500MB'; set random_page_cost=1.2; explain analyze select ... and see what you get. -- Sent via pgsql-general mailing list (pgsql-general@postgres

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

On Thursday, May 23, 2013 10:51 PM fburgess wrote: > serverdb=# set enable_hashjoin=off; > SET > 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'; >

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

On Thu, May 23, 2013 at 12:21 PM, wrote: > > But what negative impact is disabling hash joins? > doing it just for a single query, could be a tool for solving particular problems. setting it in postgresql.conf, therefore affecting all queries, is like using a hammer to change tv channel... it wi

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

serverdb=# set enable_hashjoin=off;SETserverdb=# 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--

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

On Wed, May 22, 2013 at 7:41 AM, wrote: > PostgreSQL 9.1.6 on linux > >From the numbers in your attached plan, it seems like it should be doing a nested loop from the 580 rows (it thinks) that match in SARS_ACTS_RUN against the index on sars_run_id to pull out the 3297 rows (again, it think, th

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

PostgreSQL 9.1.6 on linux Original Message Subject: Re: [PERFORM] Very slow inner join query Unacceptable latency. From: Jaime Casanova Date: Tue, May 21, 2013 2:59 pm To: Freddie Burgess Cc: psql performance list

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

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=# e

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

On Tue, May 21, 2013 at 4:53 PM, 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 sel