X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1A1219FA1F1 for ; Fri, 26 May 2006 14:47:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 82663-02 for ; Fri, 26 May 2006 14:47:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.228]) by postgresql.org (Postfix) with ESMTP id AAD0A9FA12E for ; Fri, 26 May 2006 14:47:03 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 50so121155wri for ; Fri, 26 May 2006 10:47:02 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=GCHkheQosdAWs9DbT2mdqJr6Olh2gONWr+47w4t0z5Ji+wFDE1B0nMOSo2osRbvgIc40DoR9gtrUH8kr2tXDcg50YXWmSPXDy3RZNiDcQbmlZbamejTGkbQbTTWUXLPFdMMDTGjkXIiEUp8VZI/GoJzddX1oaZmHja7M5N9VHvQ= Received: by 10.64.181.17 with SMTP id d17mr602769qbf; Fri, 26 May 2006 10:46:30 -0700 (PDT) Received: by 10.65.137.19 with HTTP; Fri, 26 May 2006 10:46:30 -0700 (PDT) Message-ID: Date: Fri, 26 May 2006 13:46:30 -0400 From: "Merlin Moncure" To: "Tom Lane" Subject: Re: is it possible to make this faster? Cc: "Postgresql Performance" In-Reply-To: <27137.1148663259@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <15919.1148590347@sss.pgh.pa.us> <16119.1148591497@sss.pgh.pa.us> <25668.1148653358@sss.pgh.pa.us> <27137.1148663259@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/496 X-Sequence-Number: 19283 On 5/26/06, Tom Lane wrote: > "Merlin Moncure" writes: > > did you have a key on a,b,c? > Yeah, I did > create index t1i on t1 (a,b,c); > Do I need to use some other syntax to get it to work? can't thing of anything, I'm running completely stock, did you do a optimize table foo? is the wind blowing in the right direction? > > select count(*) from (select a,b,max(c) group by a,b) q; > > blows the high performance case as does putting the query in a view. > I noticed that too, while trying to suppress the returning of the > results for timing purposes ... still a few bugs in their optimizer > obviously. (Curiously, EXPLAIN still claims that the index is being > used.) well, they do some tricky things pg can't do for architectural reasons but the special case is obviously hard to get right. I suppose this kinda agrues against doing all kinds of acrobatics to optimize mvcc weak cases like the above and count(*)...better to make heap access as quick as possible. > > mysql> select user_id, acc_id, max(sample_date) from usage_samples grou= p by 1,2 > > 939 rows in set (0.07 sec) > > mysql> select user_id, acc_id, max(sample_date) from usage_samples grou= p by 1,2 > > 939 rows in set (1.39 sec) oops, pasted the wrong query..case 2 should have been select user_id, acc_id, max(sample_date), disksize from usage_samples group by 1,2 illustrating what going to the heap does to the time. merlin