[PERFORM] 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_page_cost = 2.0 cpu_tuple_cost = 0.03

[PERFORM] PostgreSQL 9.3.2 Performance issues

We have 64GB of Memory on RHEL 6.4 shared_buffers = 8GB work_mem = 64MB maintenance_work_mem = 1GB effective_cache_size = 48GBI found this list of recommended parameters for memory management in PostgreSQL. About shared_buffers: Below 2GB, set to 20% of total system memory.Below 32GB, set to

[PERFORM] Update Trigger latency utilizing the IS DISTINCT FROM syntax

if we have the following trigger:CREATE TRIGGER admin_update_triggerBEFORE UPDATE ON admin_logger_overflowFOR EACH ROW WHEN ((old.start_date_time IS DISTINCT FROM new.start_date_time)) EXECUTE PROCEDURE update_logger_config();and the database call issues an: update admin_logger_overflow set

[PERFORM] autovacuum and dead tuples

We are running PostgreSQL 9.1.6 with autovacuum = on and I am reporting on dead tuples using the pgstattuple extension. Each time I run the pgstattuple package our dead tuples counts decrease. My colleague is under the impression that dead tuples are only cleaned up via vacuum full only, while I

Re: [PERFORM] Poor OFFSET performance in PostgreSQL 9.1.6

Hi Greg,The labor_task_report table is already Partitioned by this_.work_date_time and this table contains approx. 15 billion rows. The other table labor_tasks is not partitioned. I'm thinking that the size of the external sort is part of the problem. if I remove the labor_tasks table from the

[PERFORM] 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

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

We re-testedthese settings a few times after our initial test andrealized 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. Thereended up being no significantsaving by setting these

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: [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

Re: [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 ja...@2ndquadrant.com Date: Tue, May 21, 2013 2:59 pm To: Freddie Burgess fburg...@radiantblue.com Cc: psql performance list

[PERFORM] 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