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 85FAFD1B4ED for ; Thu, 13 Nov 2003 21:37:32 +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 38680-04 for ; Thu, 13 Nov 2003 17:37:03 -0400 (AST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by svr1.postgresql.org (Postfix) with ESMTP id 5D12FD1B535 for ; Thu, 13 Nov 2003 17:37:01 -0400 (AST) Received: from [10.1.2.130] (helo=dba2) by mail.libertyrms.com with esmtp (Exim 3.22 #3 (Debian)) id 1AKP9D-0002cd-00 for ; Thu, 13 Nov 2003 16:37:03 -0500 Received: by dba2 (Postfix, from userid 1019) id 4C0FFCB49; Thu, 13 Nov 2003 16:37:03 -0500 (EST) Date: Thu, 13 Nov 2003 16:37:03 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: strange estimate for number of rows Message-ID: <20031113213703.GJ25546@libertyrms.info> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <20031113181415.GB25546@libertyrms.info> <5882.1068749786@sss.pgh.pa.us> <3FB3DD1E.2000509@libertyrms.info> <11160.1068754748@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <11160.1068754748@sss.pgh.pa.us> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/102 X-Sequence-Number: 4662 On Thu, Nov 13, 2003 at 03:19:08PM -0500, Tom Lane wrote: > 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? Do you want the whole thing? I left out the really verbose bits when I posted this in the original: SELECT attname,null_frac,avg_width,n_distinct,correlation FROM pg_stats where tablename = 'transactions_posted' AND attname in ('transaction_date','product_id'); attname | null_frac | avg_width | n_distinct | correlation ------------------+-----------+-----------+------------+------------- product_id | 0 | 4 | 2 | 0.200956 transaction_date | 0 | 8 | -0.200791 | 0.289248 > > 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. Actually, this one's on an internal box, and I think 1.5 is too low -- it's really just a pair of mirrored SCSI disks on a PCI controller in this case. That does the trick, though, so maybe I'm just being too conservantive. A -- ---- Andrew Sullivan 204-4141 Yonge Street Afilias Canada Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110