Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A3FD49F9D9B for ; Thu, 1 Nov 2007 10:46:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 04753-08 for ; Thu, 1 Nov 2007 10:46:40 -0300 (ADT) Received: from mx3.hub.org (mx3.hub.org [206.223.169.73]) by postgresql.org (Postfix) with ESMTP id 778369F9B65 for ; Thu, 1 Nov 2007 10:46:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mx3.hub.org (Postfix) with ESMTP id DB43B3808B4 for ; Thu, 1 Nov 2007 10:46:47 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lA1Dhd3V004306; Thu, 1 Nov 2007 09:43:39 -0400 (EDT) To: Palle Girgensohn cc: pgsql-performance@postgresql.org Subject: Re: select max(field) from table much faster with a group by clause? In-reply-to: References: Comments: In-reply-to Palle Girgensohn message dated "Thu, 01 Nov 2007 14:07:55 +0100" Date: Thu, 01 Nov 2007 09:43:39 -0400 Message-ID: <4305.1193924619@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/12 X-Sequence-Number: 27427 Palle Girgensohn writes: > When running > select max("when") from login where userid='userid' > it takes a second or two, but when adding "group by userid" the planner > decides on using another plan, and it gets *much* faster. See example below. It's only faster for cases where there are few or no rows for the particular userid ... > Number of tuples per user varies from zero to a couple of thousands. The planner is using an intermediate estimate of 406 rows. You might be well advised to increase the statistics target for login.userid --- with luck that would help it to choose the right plan type for both common and uncommon userids. regards, tom lane