slow seqscan

From: Edoardo Ceccarelli <eddy(at)axa(dot)it>
To: pgsql-performance(at)postgresql(dot)org
Subject: slow seqscan
Date: 2004-04-21 07:17:23
Message-ID: 40862003.2090201@axa.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Nick Barr 2004-04-21 07:47:58 Re: slow seqscan
Previous Message pginfo 2004-04-21 05:22:17 Re: Wierd context-switching issue on Xeon