From: Aaron Held <aaron@MetroNY.com>
To: josh@agliodbs.com
Cc: Chris Ruprecht <chrup@earthlink.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance w/ multiple WHERE clauses
Date: Mon, 23 Sep 2002 09:31:33 -0400
Message-ID: <3D8F17B5.60902@MetroNY.com> (raw)
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>
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.
>
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-sql@postgresql.org
Cc: aaron@MetroNY.com, josh@agliodbs.com, chrup@earthlink.net
Subject: Re: Performance w/ multiple WHERE clauses
In-Reply-To: <3D8F17B5.60902@MetroNY.com>
* 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