X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id AC660D1B49F for ; Mon, 12 Jan 2004 04:06:22 +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 75261-02 for ; Mon, 12 Jan 2004 00:05:51 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 0A2BCD1B7ED for ; Mon, 12 Jan 2004 00:05:51 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i0C45nU8038339 for ; Mon, 12 Jan 2004 04:05:50 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i0C44ref037187 for pgsql-performance@postgresql.org; Mon, 12 Jan 2004 04:04:53 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: annoying query/planner choice Date: Sun, 11 Jan 2004 22:56:59 -0500 Organization: cbbrowne Computing Inc Lines: 42 Message-ID: References: <22837C75-44AC-11D8-8262-000393A47FCC@ravensfield.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:kVlrcw0iF1ckQouIwioUuVc8GL0= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/119 X-Sequence-Number: 5294 Centuries ago, Nostradamus foresaw when ronz@ravensfield.com (Andrew Rawnsley) would write: > I would like, of course, for it to use the index, given that it > takes 20-25% of the time. Fiddling with CPU_TUPLE_COST doesn't do > anything until I exceed 0.5, which strikes me as a bit high (though > please correct me if I am assuming too much...). RANDOM_PAGE_COST > seems to have no effect. I suppose I could cluster it, but it is > constantly being added to, and would have to be re-done on a daily > basis (if not more). > > Any suggestions? The apparent problem is a bad query plan, and for clustering to "fix" it seems a disturbing answer. A problem I saw last week with some query plans pointed to the issue that the statistics were inadequate. We had some queries where indexing on "customer" is extremely worthwhile in nearly all cases, but it often wasn't happening. The problem was that the 10 "bins" in the default stats table would collect up stats about a few _highly_ active customers, and pretty much ignore the less active ones. Because the "bins" were highly dominated by the few common values, stats for the others were missing and pretty useless. I upped the size of the histogram from 10 to 100, and that allowed stats to be kept for less active customers, GREATLY improving the quality of the queries. The point that falls out is that if you have a column which has a bunch of discrete values (rather more than 10) that aren't near-unique (e.g. - on a table with a million transactions, you have a only few hundred customers), that's a good candidate for upping column stats. Thus, you might try: ALTER TABLE MY_TABLE ALTER COLUMN SOME_COLUMN SET STATISTICS 50; ANALYZE MY_TABLE; -- let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/postgresql.html "There's no longer a boycott of Apple. But MacOS is still a proprietary OS." -- RMS - June 13, 1998