From: Nick Barr <nicky@chuckie.co.uk>
To: Edoardo Ceccarelli <eddy@axa.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow seqscan
Date: Wed, 21 Apr 2004 08:47:58 +0100
Message-ID: <4086272E.2080102@chuckie.co.uk> (raw)
In-Reply-To: <40862003.2090201@axa.it>
References: <40862003.2090201@axa.it>
Edoardo Ceccarelli wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
In general we are going to need more information, like what kind of
search filters you are using on the text field and an EXPLAIN ANALYZE.
But can you try and run the following, bearing in mind it will take a
while to complete.
REINDEX TABLE <table_name>
From what I remember there were issues with index space not being
reclaimed in a vacuum. I believe this was fixed in 7.4. By not
reclaiming the space the indexes grow larger and larger over time,
causing PG to prefer a sequential scan over an index scan (I think).
Hope that helps
Nick
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nicky@chuckie.co.uk, eddy@axa.it
Subject: Re: slow seqscan
In-Reply-To: <4086272E.2080102@chuckie.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox