Message-ID: <3D8F17B5.60902@MetroNY.com>
Date: Mon, 23 Sep 2002 09:31:33 -0400
From: Aaron Held <aaron@MetroNY.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.0.1) Gecko/20020823 Netscape/7.0
MIME-Version: 1.0
To: josh@agliodbs.com
Cc: Chris Ruprecht <chrup@earthlink.net>, pgsql-sql@postgresql.org
Subject: Re: Performance w/ multiple WHERE clauses
References: <5.1.1.6.2.20020918111926.00a82058@mail.nashlinux.com>
	<200209182039.59437.chrup@earthlink.net>
	<3D8B2C02.8020904@MetroNY.com>
	<200209201455.30054.josh@agliodbs.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I am running pg 7.2 the PG reference build.

Thanks for the ANALYZE tip,  it led me to a answer.

This database gets a monthly update and it read only until the next 
update.  I ANALYZE once after each update.  Since the data does not 
change I should not need to ANALYZE again afterwards.

I mentioned this to the dbadmin that manages the data and found out one 
of the other users UPDATED some of the columns the morning that I was 
seeing this behavior.

I'll reANALYZE and see what happens.

Thanks,
-Aaron Held


Josh Berkus wrote:
> Aaron,
> 
> 
>>	# SET enable_seqscan to FALSE ;
>>	forced the use of an Index and sped things up greatly.
>>
>>I am not sure why it made the switch.  The load on the server seems to 
>>affect the performance, but I am seeing it more on the production server 
>>with 100 million rows as opposed to the development server with only 
>>about 6 million.  I need to buy more drives and develop on a larger data 
>>set.
> 
> 
> What version are you using?
> 
> I'd have 3 suggestions:
> 1) ANALYZE, ANALYZE, ANALYZE.  Then check if the row estimates made by EXPLAIN 
> seem accurate.
> 2) Modify your postgresql.conf file to raise the cost of seq_scans for parser 
> estimates.
> 3) Test this all again when 7.3 comes out, as parser estimate improves all the 
> time.
>