X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9049B9FB1D8 for ; Tue, 11 Jul 2006 14:14:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 89539-08 for ; Tue, 11 Jul 2006 14:14:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from bay0-omc2-s38.bay0.hotmail.com (bay0-omc2-s38.bay0.hotmail.com [65.54.246.174]) by postgresql.org (Postfix) with ESMTP id 2F71C9FB1C6 for ; Tue, 11 Jul 2006 14:14:17 -0300 (ADT) Received: from hotmail.com ([65.54.162.32]) by bay0-omc2-s38.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 11 Jul 2006 10:14:17 -0700 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 11 Jul 2006 10:14:16 -0700 Message-ID: Received: from 65.54.162.200 by by108fd.bay108.hotmail.msn.com with HTTP; Tue, 11 Jul 2006 17:14:16 GMT X-Originating-IP: [64.237.4.140] X-Originating-Email: [amushayeed@hotmail.com] X-Sender: amushayeed@hotmail.com From: "Abu Mushayeed" To: pgsql-admin@postgresql.org Subject: PG slowdown Date: Tue, 11 Jul 2006 10:14:16 -0700 Mime-Version: 1.0 Content-Type: text/plain; format=flowed X-OriginalArrivalTime: 11 Jul 2006 17:14:16.0905 (UTC) FILETIME=[709BBB90:01C6A50D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/71 X-Sequence-Number: 22317 Hello, My hardware CPU: Intel 3 GHZ X 4 Memory: 9 GB LINUX My conf file are as follows: shared_buffers=10000 wal_buffers=128 vacuum_mem = 256000 sort_mem=32768 max_fsm_pages = 3000000 max_fsm_relations = 32768 checkpoint_segments = 2048 checkpoint_timeout = 1800 checkpoint_warning = 300 effective_cache_size = 262144 random_page_cost = 2 default_statistics_target = 250 Some of my queries that used to run within a minute, before I changed the "default_statistics_target" from 10 to 250, now runs for 20-30 minutes. Some queries did see improvement in their run time. I have two questions: 1. Why did the other queries slowdown? I tried to tune them, as their is a join between two tables and it reads all the rows, I forced it to do a seq scan rather than index scan but it quadrupled it run time. Why? 2. How can I disable the logging of updates? What are the pitfalls? When an update or insert happening there are lot of logging going on and the system writes messages like this "2006-07-11 10:08:54 [2330] LOG: checkpoints are occurring too frequently (182 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments"." Thank you for the help. Abu