From: Anne Rosset <arosset@collab.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need help with one query
Date: Mon, 23 Mar 2009 10:08:59 -0700
Message-ID: <49C7C22B.9050003@collab.net> (raw)
In-Reply-To: <12521.1237595840@sss.pgh.pa.us>
References: <49C2AC75.5060601@collab.net>
<49C3600E.20504@archonet.com>
<49C3CF52.9090602@collab.net>
<603c8f070903201204s53f2a3cvbf9afc8649b2201a@mail.gmail.com>
<12521.1237595840@sss.pgh.pa.us>
Tom Lane wrote:
>Robert Haas <robertmhaas@gmail.com> writes:
>
>
>>On Fri, Mar 20, 2009 at 1:16 PM, Anne Rosset <arosset@collab.net> wrote:
>>
>>
>>>The db version is 8.2.4
>>>
>>>
>
>
>
>>Something is wrong here. How can setting enable_seqscan to off result
>>in a plan with a far lower estimated cost than the original plan?
>>
>>
>
>Planner bug no doubt ... given how old the PG release is, I'm not
>particularly interested in probing into it now. If Anne can still
>reproduce this behavior on 8.2.something-recent, we should look closer.
>
> regards, tom lane
>
>
Thanks Tom, Richard.
Here are our postgres conf :
shared_buffers = 2000MB
sort_mem = 150000
vacuum_mem = 100000
work_mem = 20MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
max_fsm_pages = 204800
full_page_writes = off # recover from partial page writes
wal_buffers = 1MB # min 32kB
# (change requires restart)
commit_delay = 20000 # range 0-100000, in microseconds
commit_siblings = 3 # range 1-1000
checkpoint_segments = 128
checkpoint_warning = 240s
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = off
random_page_cost = 2.0
effective_cache_size = 2500MB
geqo = off
default_statistics_target = 750
stats_command_string = on
update_process_title = on
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_vacuum_threshold = 500 # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 250 # min # of tuple updates before
# analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.1
Anne
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: arosset@collab.net, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, dev@archonet.com
Subject: Re: Need help with one query
In-Reply-To: <49C7C22B.9050003@collab.net>
* 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