Skip site navigation (1) Skip section navigation (2)

Re: slow seqscan

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Edoardo Ceccarelli <eddy(at)axa(dot)it>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow seqscan
Date: 2004-04-21 07:52:55
Message-ID: 40862857.4030008@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Edoardo,

> 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.

Please paste the exact SELECT query that uses a seqscan, plus the 
EXPLAIN ANALYZE of the SELECT, and the psql output of \d <table>.

> 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.

After such an update, you need to run VACUUM ANALYZE <table>;  Run it 
before the update as well, if it doesn't take that long.

> 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.

You need to vacuum analyze (NOT full) once and HOUR, not once a month. 
Add this command to your crontab to run once an hour and verify that 
it's working:

vacuumdb -a -z -q

Otherwise, install the auto vacuum utility found in 
contrib/pg_autovacuum in the postgres source.  Set this up.  It will 
monitor postgres and run vacuums and analyzes when necessary.  You can 
then remove your cron job.

> 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?

There's no read only mode to improve performance.

Upgrading to 7.4 will more than likely improve the performance of your 
database in general.  Be careful to read the upgrade notes because there 
were a few incompatibilities.

Chris


In response to

  • slow seqscan at 2004-04-21 07:17:23 from Edoardo Ceccarelli

pgsql-performance by date

Next:From: Nick BarrDate: 2004-04-21 08:31:39
Subject: MySQL vs PG TPC-H benchmarks
Previous:From: Nick BarrDate: 2004-04-21 07:47:58
Subject: Re: slow seqscan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group