X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 651BD3A437D for ; Wed, 17 Nov 2004 22:35:52 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45365-03 for ; Wed, 17 Nov 2004 22:35:41 +0000 (GMT) Received: from cmailm2.svr.pol.co.uk (cmailm2.svr.pol.co.uk [195.92.193.210]) by svr1.postgresql.org (Postfix) with ESMTP id 14E323A44FC for ; Wed, 17 Nov 2004 22:35:42 +0000 (GMT) Received: from modem-2210.llama.dialup.pol.co.uk ([217.135.184.162] helo=[192.168.0.102]) by cmailm2.svr.pol.co.uk with esmtp (Exim 4.41) id 1CUYOr-0000dk-At; Wed, 17 Nov 2004 22:35:41 +0000 Subject: Re: Analyzer is clueless From: Simon Riggs To: David Brown Cc: pgsql-performance@postgresql.org In-Reply-To: <20041117160824.B694D3A3ECC@svr1.postgresql.org> References: <20041117160824.B694D3A3ECC@svr1.postgresql.org> Content-Type: text/plain Organization: 2nd Quadrant Message-Id: <1100730768.4113.7709.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 17 Nov 2004 22:32:48 +0000 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/268 X-Sequence-Number: 9222 On Thu, 2004-11-18 at 02:08, David Brown wrote: > Statistics were run on each table before query execution. The random page cost was lowered to 2, but as you can see, the estimated costs are wild anyway. > > As a comparison, MS SQL Server took less than 15 seconds, or 4 times faster. > > MySQL (InnoDB) took 2 seconds, which is 30 times faster. > > The query looks straightforward to me (it might be clearer with a subselect), so what on earth is wrong? The query is, as you say, straightforward. You are clearly working with a query that is on the very edge of the decision between using an index or not. The main issue is that PostgreSQL's default histogram statistics setting is lower than other RDBMS. This means that it is less able to discriminate between cases such as yours that are close to the edge. This is a trade-off between run-time of the ANALYZE command and the benefit it produces. As Joshua suggests, increasing the statistics target for this table will likely allow the optimizer to correctly determine the selectivity of the index and take the right path. If this is a general RDBMS comparison, you may wish to extend the system's default_statistics_target = 80 or at least > 10. To improve this query, you may wish to extend the table's statistics target using: ALTER TABLE "drinv" ALTER COLUMN OrderDate SET STATISTICS 100; which should allow the planner to more accurately estimate statistics and thereby select an index, if appropriate. The doco has recently been changed with regard to effective_cache_size; you don't mention what beta release level you're using. That is the only planner parameter that takes cache size into account, so any other changes would certainly have zero effect on this *plan* though might still benefit execution time. Please post EXPLAIN ANALYZE output for any further questions. -- Best Regards, Simon Riggs