[GENERAL] Very slow inner join query Unacceptable latency.

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)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

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.

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.

1.) Server settingmemory: 32960116kB = 32GB2.) Current Postgresql configuration settings of note in my environment.enable_hashjoin=offwork_mem = 16MB #random_page_cost-4.0 <- defaultmaintenance_work_mem=256MBshared_buffers = 8GBserverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_

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

[GENERAL] Evaluating query performance with caching in PostgreSQL 9.1.6

Two questions Please1.) Is there any way to clear the cache so that we can ensure that when we run "explain analyze" on a query and make some minor adjustments to that query and re-execute, the plan is not cached. Since the cached plan returns runtimes that are much lower than the initial execution

[GENERAL] Upgrading from postgreSQL 9.1.6 to 9.3

Are there any showstoppers/recommendations/experiences with upgrading from Postgres 9.1.6 Postgis 1.5.3 to PostGres 9.3 and associated PostGIS version XX on rhel 6.4 that will help the process go smoothly.thanks

Re: [GENERAL] Upgrading from postgreSQL 9.1.6 to 9.3

1.) We have to upgrade four 9.1 database instances, so is using pg_upgrade still the way to go? Our prior upgrade methodology when we moved from pg 8.4.3 to 9.1.6 was to use the hard links install option. We also have our data spread across storage mediums; fiber, nas. Do these things factor in?2.)

[GENERAL] Very slow query in PostgreSQL 9.3.3

PostgreSQL 9.3.3 RHEL 6.4Total db Server memory 64GB# -# PostgreSQL configuration file# -max_connections = 100shared_buffers = 16GBwork_mem = 32MB maintenance_work_mem = 1GBseq_page_cost = 1.0   random_p