Hi, Sorry for the late answer. I found the query i was looking for in the log (duration) and could prove that the seqscan is faster if the data were not cached. This particular one was 22% faster. It is "a query which will get turned into a nested loop index scan for a lot of rows, on a huge table", but it's only 22% slower without a seqscan. (there's no advantage with seqscans off, as long as the cache is empty) I found few queries that did sequential scans in the normal mode on tables that matter. I found one query that did a seqscan anyway(with enable_seqscan off), because doing an index scan would be more than 1M points more expensive (to the planner). $ grep ^[^#] /etc/postgresql/8.3/main/postgresql.conf|grep -e ^[^[:space:]] data_directory =<blah> # use data in another directory hba_file = <blah> # host-based authentication file ident_file = <blah> # ident configuration file external_pid_file = <blah> # write an extra PID file listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 200 # (change requires restart) unix_socket_directory = '/var/run/postgresql' # (change requires restart) tcp_keepalives_idle = 120 # TCP_KEEPIDLE, in seconds; tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; tcp_keepalives_count = 0 # TCP_KEEPCNT; shared_buffers = 2GB # min 128kB or max_connections*16kB temp_buffers = 24MB # min 800kB work_mem = 100MB # min 64kB maintenance_work_mem = 256MB # min 1MB max_fsm_pages = 600000 # min max_fsm_relations*16, 6 bytes each synchronous_commit = off # immediate fsync at commit checkpoint_segments = 16 # in logfile segments, min 1, 16MB each effective_cache_size = 4GB log_min_duration_statement = 2000 # -1 is disabled, 0 logs all statements --> milliseconds log_line_prefix = '%t ' # special values: autovacuum = on # Enable autovacuum subprocess? 'on' datestyle = 'iso, mdy' lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting default_text_search_config = 'pg_catalog.english' max_locks_per_transaction = 128 # min 10 We have 15K rpm SAS disks in RAID10. We have 16 GB of RAM and 4 modern processor cores (i think xeons, might also be opteron) We run Debian Lenny. It's a dedicated DB server, there is one other cluster on it without very much data and with few connections to it daily. df -h on the data dir gives me 143G we're growing there are many queries that should be optimized the seqscan option is in the connection string, not in the postgresql.conf Cheers, On Fri, Aug 27, 2010 at 7:57 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On Thu, Aug 26, 2010 at 4:32 AM, Willy-Bas Loos <willybas@xxxxxxxxx> wrote: >> Hi, >> >> I have a colleague that is convinced that the website is faster if >> enable_seqscan is turned OFF. >> I'm convinced of the opposite (better to leave it ON), but i would like to >> show it, prove it to him. > > Stop, you're both doing it wrong. The issue isn't whether or not > turning off seq scans will make a few things faster here and there, > it's why is the query planner choosing sequential scans when it should > be choosing index scans. > > So, what are your non-default settings in postgresql.conf? > Have you increased effective_cache_size yet? > Lowered random_page_cost? > Raised default stats target and re-analyzed? > > Have you been looking at the problem queries with explain analyze? > What does it have to say about the planners choices? > -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
