Re: Need help with one query

From: Anne Rosset
Subject: Re: Need help with one query
Date: ,
Msg-id: 49C7C22B.9050003@collab.net
(view: Whole thread, Raw)
In response to: Re: Need help with one query  (Tom Lane)
Responses: Re: Need help with one query  (Robert Haas)
List: pgsql-performance

Tree view

Need help with one query  (Anne Rosset, )
 Re: Need help with one query  (Richard Huxton, )
  Re: Need help with one query  (Tom Lane, )
  Re: Need help with one query  (Anne Rosset, )
   Re: Need help with one query  (Robert Haas, )
    Re: Need help with one query  (Alvaro Herrera, )
     Re: Need help with one query  (Anne Rosset, )
      Re: Need help with one query  (Robert Haas, )
    Re: Need help with one query  (Tom Lane, )
     Re: Need help with one query  (Anne Rosset, )
      Re: Need help with one query  (Robert Haas, )
       Re: Need help with one query  (Anne Rosset, )

Tom Lane wrote:

>Robert Haas <> writes:
>
>
>>On Fri, Mar 20, 2009 at 1:16 PM, Anne Rosset <> 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


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: multiple threads inserting into the same table
From: Sergey Burladyan
Date:
Subject: Why creating GIN table index is so slow than inserting data into empty table with the same index?