X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 494B7D1D163 for ; Wed, 21 Apr 2004 04:17:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 21391-10 for ; Wed, 21 Apr 2004 04:17:24 -0300 (ADT) Received: from FE-mail03.sfg.albacom.net (FE-mail03.albacom.net [213.217.149.83]) by svr1.postgresql.org (Postfix) with ESMTP id B62A2D1CCB1 for ; Wed, 21 Apr 2004 04:17:22 -0300 (ADT) Received: from axa.it (217.221.80.72) by FE-mail03.sfg.albacom.net (7.0.009) id 40757CA50010913C for pgsql-performance@postgresql.org; Wed, 21 Apr 2004 09:17:24 +0200 Message-ID: <40862003.2090201@axa.it> Date: Wed, 21 Apr 2004 09:17:23 +0200 From: Edoardo Ceccarelli User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: it, en, en-us MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: slow seqscan Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/332 X-Sequence-Number: 6632 My first post to this list :) Scenario: I have a database used only with search queries with only one table that holds about 450.000/500.000 records. The table is well indexed so that most of the queries are executed with index scan but since there is a big text field in the table (360chars) some search operation (with certain filters) ends up with seq scans. This table is not written during normal operation: twice per week there is a batch program that insert about 35.000 records and updates another 40.000. last friday morning, after that batch has been executed, the database started responding really slowly to queries (expecially seq scans), after a "vacuum full analize" things did get something better. Yesterday the same: before the batch everything was perfect, after every query was really slow, I've vacuum it again and now is ok. Since now the db was working fine, it's 4 month's old with two updates per week and I vacuum about once per month. I am using version 7.3 do I need to upgrade to 7.4? also, I was thinking about setting this table in a kind of "read-only" mode to improve performance, is this possible? Thank you for your help Edoardo Ceccarelli