X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4AE1CD1B550 for ; Fri, 29 Aug 2003 02:56:05 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 71477-04 for ; Thu, 28 Aug 2003 23:55:58 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 18C2BD1B8AD for ; Thu, 28 Aug 2003 23:55:54 -0300 (ADT) Received: (qmail 5140 invoked by uid 500); 29 Aug 2003 03:01:56 -0000 Date: Thu, 28 Aug 2003 22:01:56 -0500 From: Bruno Wolff III To: Ken Geis Cc: pgsql-performance@postgresql.org Subject: Re: bad estimates / non-scanning aggregates Message-ID: <20030829030156.GF4373@wolff.to> Mail-Followup-To: Ken Geis , pgsql-performance@postgresql.org References: <3F4E99F7.7080604@speakeasy.org> <20030829025109.GD4373@wolff.to> <3F4EBF7E.8020602@speakeasy.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3F4EBF7E.8020602@speakeasy.org> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/460 X-Sequence-Number: 3276 On Thu, Aug 28, 2003 at 19:50:38 -0700, Ken Geis wrote: > Bruno Wolff III wrote: > >On Thu, Aug 28, 2003 at 17:10:31 -0700, > > Ken Geis wrote: > > > >>The query I want to run is > >> > >>select stock_id, min(price_date) from day_ends group by stock_id; > > > >The fast way to do this is: > > > >select distinct on (stock_id) stock_id, price_date > > order by stock_id, price_date; > > Not according to the optimizer! Plus, this is not guaranteed to return > the correct results. For it to be fast you need an index on (stock_id, price_date) so that you can use an index scan. The answers are guarenteed to be correct. See: http://developer.postgresql.org/docs/postgres/sql-select.html#SQL-DISTINCT