X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2A70ED1D1C3 for ; Thu, 5 Feb 2004 17:10:01 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 81494-09 for ; Thu, 5 Feb 2004 13:09:53 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 2EE62D1BCA7 for ; Thu, 5 Feb 2004 13:09:52 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i15H9n5b004194; Thu, 5 Feb 2004 12:09:50 -0500 (EST) To: Christopher Browne Cc: pgsql-admin@postgresql.org Subject: Re: R: R: slow seqscan after vacuum analize In-reply-to: References: <2s28b9$kqpmo@mailr-1.tiscali.it> Comments: In-reply-to Christopher Browne message dated "Wed, 04 Feb 2004 23:18:39 -0500" Date: Thu, 05 Feb 2004 12:09:49 -0500 Message-ID: <4193.1076000989@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/60 X-Sequence-Number: 12234 Christopher Browne writes: > Another factor worth considering: If a few values are very common in > the field you are selecting on, then the query optimizer can get > convinced (wrongly) that a Seq Scan is the best choice. Using ALTER > TABLE T ALTER COLUMN C SET STATISTICS [some value] to increase the > number of "bins" can be helpful in such cases. (My pet theory is that > the present default value of 10 is a little low, and that a lot of > optimizer errors might be resolved by bumping it up a bit...) I also suspect that 10 is a lowball figure, but no one has done any work to establish what might be a more reasonable default. Larger values have real costs in both pg_statistic space and planner runtime, so I don't want to push it up without some kind of evidence. BTW, if you think a higher default might be appropriate, you can set it in postgresql.conf instead of running around and manually ALTERing all your tables. regards, tom lane