X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B7E77DB971 for ; Thu, 3 Nov 2005 15:11:50 -0400 (AST) 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 47070-01 for ; Thu, 3 Nov 2005 19:11:45 +0000 (GMT) X-Greylist: delayed 00:20:35.173706 by SQLgrey- Received: from calvin.slamb.org (calvin.slamb.org [216.136.66.56]) by svr1.postgresql.org (Postfix) with ESMTP id 821E2DB972 for ; Thu, 3 Nov 2005 15:10:28 -0400 (AST) Received: by calvin.slamb.org (Postfix, from userid 103) id 8B08E6FD1E; Thu, 3 Nov 2005 12:49:53 -0600 (CST) Received: from [??@IPv6:::1] (localhost.slamb.org [127.0.0.1]) by calvin.slamb.org (Postfix) with ESMTP id 7DA096FD03; Thu, 3 Nov 2005 12:49:51 -0600 (CST) In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD79E@Herge.rcsinc.local> References: <6EE64EF3AB31D5448D0007DD34EEB3417DD79E@Herge.rcsinc.local> Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: , "Dustin Sallings" Content-Transfer-Encoding: 7bit From: Scott Lamb Subject: Re: Sorted union Date: Thu, 3 Nov 2005 10:49:50 -0800 To: "Merlin Moncure" X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/53 X-Sequence-Number: 15310 On Nov 3, 2005, at 10:21 AM, Merlin Moncure wrote: > Reading the previous paragraphs I was just about to suggest this. > This > is a much more elegant method...you are reaping the benefits of having > normalized your working set. You were trying to denormalize it > back to > what you were used to. Yes, now you can drop your index and simplify > your queries...normalized data is always more 'natural'. I'm not sure normalized is the right word. In either case, I'm storing it in the same form. In either case, my ConcurrencyProcessor class gets the same form. The only difference is if the database splits the rows or if my application does so. But we're essentially agreed. This is the algorithm I'm going to try implementing, and I think it will work out well. It also means sending about half as much data from the database to the application. >> Mind you, I still think PostgreSQL should be able to perform that >> sorted union fast. Maybe sometime I'll have enough free time to take >> my first plunge into looking at a database query planner. > > I'm not so sure I agree, by using union you were basically pulling two > independent sets (even if they were from the same table) that > needed to > be ordered. Yes. > There is zero chance of using the index here for ordering > because you are ordering a different set than the one being indexed. I don't think that's true. It just needs to look at the idea of independently ordering each element of the union and then merging that, compared to the cost of grabbing the union and then ordering it. In this case, the former cost is about 0 - it already has independently ordered them, and the merge algorithm is trivial. Regards, Scott -- Scott Lamb