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

Re: Need help with one query

From: Anne Rosset <arosset(at)collab(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Richard Huxton <dev(at)archonet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Need help with one query
Date: 2009-03-23 17:08:59
Message-ID: 49C7C22B.9050003@collab.net (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:

>Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>  
>
>>On Fri, Mar 20, 2009 at 1:16 PM, Anne Rosset <arosset(at)collab(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2009-03-23 17:15:20
Subject: Re: Need help with one query
Previous:From: Tom LaneDate: 2009-03-23 14:26:38
Subject: Re: Slower query after psql changed it's execution plan

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