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

Re: slow seqscan

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: eddy(at)expot(dot)it
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: slow seqscan
Date: 2004-04-20 16:46:16
Message-ID: 1082479576.1557.173.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-jdbc
Edoardo,

You would be better to post this to the performance list, but in the
meantime you should check 

you have enough shared buffers for the query, and that your effective
cache size is big enough.

Also a vacuum analyze is all that is really required, full will recover
dead tuples, but doesn't really speed anything up.
Dave
On Fri, 2004-04-16 at 18:58, Edoardo Ceccarelli wrote:
> I have already posted a similar topic months ago, that time I had a 
> strange behaviour checking response time of seqscan after a vacuum, but 
> now has happened again in another situation.
> 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 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.
> 
> this morning, after that batch has been executed, the database started 
> responding really slowly to seq scan  queries, also after a "vacuum full 
> analize" things didn't get better.
> 
> 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 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 
> 
> 
> !DSPAM:40854dd6304611255714539!
> 
> 
-- 
Dave Cramer
519 939 0336
ICQ # 14675561


In response to

  • slow seqscan at 2004-04-16 22:58:42 from Edoardo Ceccarelli

pgsql-jdbc by date

Next:From: Dave CramerDate: 2004-04-20 16:50:57
Subject: Re: How do handle concurrent DML operations
Previous:From: James RobinsonDate: 2004-04-20 15:35:39
Subject: Re: Prepared Statements and large where-id-in constant blocks?

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