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 54F97D1B4E1 for ; Thu, 13 Nov 2003 19:43: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 16175-08 for ; Thu, 13 Nov 2003 15:42:30 -0400 (AST) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 69341D1B522 for ; Thu, 13 Nov 2003 15:42:29 -0400 (AST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by www.postgresql.com (Postfix) with ESMTP id A5679CF48D4 for ; Thu, 13 Nov 2003 15:42:28 -0400 (AST) Received: from dev7.int.libertyrms.com ([10.1.2.225] helo=libertyrms.info) by mail.libertyrms.com with esmtp (Exim 3.22 #3 (Debian)) id 1AKNHK-0000hW-00; Thu, 13 Nov 2003 14:37:18 -0500 Message-ID: <3FB3DD1E.2000509@libertyrms.info> Date: Thu, 13 Nov 2003 14:35:58 -0500 From: Daniel Manley User-Agent: Mozilla/5.0 (X11; U; Linux i686; ru-RU; rv:1.5) Gecko/20031016 X-Accept-Language: en, fr, en-us MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: strange estimate for number of rows References: <20031113181415.GB25546@libertyrms.info> <5882.1068749786@sss.pgh.pa.us> In-Reply-To: <5882.1068749786@sss.pgh.pa.us> Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/100 X-Sequence-Number: 4660 Hi, I'm the lead developer on the project this concerns (forgive my newbiness on this list). 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. explain analyse select * from tablename where transaction_date >= '2003-9-1' and transaction_date < '2003-10-1'; ----------------------------------------------------------------------------------------------------------------------------------- Seq Scan on tablename (cost=0.00..348199.14 rows=1180724 width=91) (actual time=7727.668..36286.898 rows=579238 loops=1) Filter: ((transaction_date >= '2003-09-01 00:00:00+00'::timestamp with time zone) AND (transaction_date < '2003-10-01 00:00:00+00'::timestamp with time zone)) Total runtime: 36625.351 ms explain analyse select * from transactions_posted where product_id = 2; ----------------------------------------------------------------------------------------------------------------------------------- Seq Scan on transactions_posted (cost=0.00..319767.95 rows=6785237 width=91) (actual time=0.091..35596.328 rows=5713877 loops=1) Filter: (product_id = 2) Total runtime: 38685.373 ms The product_id alone gives a difference of a millions rows from estimate to actual, vs. the factor of 2 from the transaction_date. Dan Manley Tom Lane пишет: >Andrew Sullivan writes: > > >>The statistics on transaction_date and product_id are set to 1000. >>Everything is all analysed nicely. But I'm getting a poor plan, >>because of an estimate that the number of rows to be returned is >>about double how many actually are: >> >> > > > >>explain analyse select * from transactions_posted where >>transaction_date >= '2003-9-1' and transaction_date < '2003-10-1' and >>product_id = 2; >> >> > >Are the estimates accurate for queries on the two columns individually, >ie >... where transaction_date >= '2003-9-1' and transaction_date < '2003-10-1' >... where product_id = 2 > >If so, the problem is that there's a correlation between >transaction_date and product_id, which the system cannot model because >it has no multi-column statistics. > >However, given that the estimate is only off by about a factor of 2, >you'd still be getting the wrong plan even if the estimate were perfect, >because the estimated costs differ by nearly a factor of 3. > >Given the actual runtimes, I'm thinking maybe you want to reduce >random_page_cost. What are you using for that now? > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > >