X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C6E11D1B559 for ; Thu, 13 Nov 2003 20:19:39 +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 19869-05 for ; Thu, 13 Nov 2003 16:19:10 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id A20CED1B522 for ; Thu, 13 Nov 2003 16:19:08 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hADKJ819011161; Thu, 13 Nov 2003 15:19:09 -0500 (EST) To: Daniel Manley Cc: pgsql-performance@postgresql.org Subject: Re: strange estimate for number of rows In-reply-to: <3FB3DD1E.2000509@libertyrms.info> References: <20031113181415.GB25546@libertyrms.info> <5882.1068749786@sss.pgh.pa.us> <3FB3DD1E.2000509@libertyrms.info> Comments: In-reply-to Daniel Manley message dated "Thu, 13 Nov 2003 14:35:58 -0500" Date: Thu, 13 Nov 2003 15:19:08 -0500 Message-ID: <11160.1068754748@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/101 X-Sequence-Number: 4661 Daniel Manley writes: > The product_id alone gives a difference of a millions rows from estimate > to actual, vs. the factor of 2 from the transaction_date. You should be thinking in terms of ratios, not absolute difference. The rows estimate for product_id doesn't look too bad to me; the one for transaction_date is much further off (a factor of 2). Which is odd, because the system can usually do all right on range estimates if you've let it run an ANALYZE with enough histogram bins. Could we see the pg_stats row for transaction_date? > We tried a couple of scenarios with effective_cache_size=60000, > cpu_index_tuple_cost=0.0001 and random_page_cost=2 with no change in the > plan. Since you need about a factor of 3 change in the cost estimate to get it to switch plans, changing random_page_cost by a factor of 2 ain't gonna do it (the other two numbers are second-order adjustments unlikely to have much effect, I think). Try 1.5, or even less ... of course, you have to keep an eye on your other queries and make sure they don't go nuts, but from what I've heard about your hardware setup a low random_page_cost isn't out of line with the physical realities. regards, tom lane