X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 4F7C311F6CFB for ; Tue, 18 Apr 2006 19:34:49 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.599 X-Spam-Level: X-Spam-Status: No, score=-2.599 required=5 tests=[BAYES_00=-2.599] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id SkX-uKxp3Sgb for ; Tue, 18 Apr 2006 19:34:47 -0300 (ADT) X-Greylist: delayed 00:25:59.458818 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id AC24F11F6CBA for ; Tue, 18 Apr 2006 19:34:33 -0300 (ADT) Received: from mx1.vodamail.co.za (mx1.vodamail.co.za [196.11.146.148]) by svr4.postgresql.org (Postfix) with ESMTP id 742145AF663 for ; Tue, 18 Apr 2006 22:08:33 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by mx1.vodamail.co.za (Postfix) with ESMTP id A3AD267822 for ; Wed, 19 Apr 2006 00:08:24 +0200 (SAST) Received: from mx1.vodamail.co.za ([127.0.0.1]) by localhost (mx1.vodamail.co.za [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08648-10 for ; Wed, 19 Apr 2006 00:08:24 +0200 (SAST) Received: from josh.flame2.flame.co.za (unknown [10.48.102.43]) by mx1.vodamail.co.za (Postfix) with ESMTP id 6B3D8663C9 for ; Wed, 19 Apr 2006 00:08:20 +0200 (SAST) Received: from [127.0.0.1] (localhost.localdomain [127.0.0.1]) by josh.flame2.flame.co.za (8.12.11/8.12.11) with ESMTP id k3IM7tv4003083 for ; Wed, 19 Apr 2006 00:07:59 +0200 Subject: Multicolumn order by From: Theo Kramer Reply-To: theo@flame.co.za To: pgsql-performance@postgresql.org Content-Type: text/plain Organization: Flame Computing Enterprises cc Message-Id: <1145398075.3048.18.camel@josh> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 19 Apr 2006 00:07:55 +0200 Content-Transfer-Encoding: 7bit X-Virus-Scanned: amavisd-new at vodamail.co.za X-Archive-Number: 200604/404 X-Sequence-Number: 18488 Hi Apologies if this has already been raised... PostgreSQL 8.1.3 and prior versions. Vacuum done. Assuming a single table with columns named c1 to cn and a requirement to select from a particular position in multiple column order. The column values in my simple example below denoted by 'cnv' a typical query would look as follows select * from mytable where (c1 = 'c1v' and c2 = 'c2v' and c3 >= 'c3v') or (c1 = 'c1v' and c2 > 'c2v') or (c1 > 'c1v') order by c1, c2, c3; In real life with the table containing many rows (>9 Million) and a single multicolumn index on the required columns existing I get the following explain analyse SELECT tran_subledger, tran_subaccount, tran_mtch, tran_self, tran_Rflg FROM tran WHERE ((tran_subledger = 2 AND tran_subaccount = 'ARM ' AND tran_mtch = 0 AND tran_self >= 0 ) OR (tran_subledger = 2 AND tran_subaccount = 'ARM ' AND tran_mtch > 0 ) OR (tran_subledger = 2 AND tran_subaccount > 'ARM ' ) OR (tran_subledger > 2 )) ORDER BY tran_subledger, tran_subaccount, tran_mtch, tran_self limit 10; Limit (cost=0.00..25.21 rows=10 width=36) (actual time=2390271.832..2390290.305 rows=10 loops=1) -> Index Scan using tran_mtc_idx on tran (cost=0.00..13777295.04 rows=5465198 width=36) (actual time=2390271.823..2390290.252 rows=10 loops=1) Filter: (((tran_subledger = 2) AND (tran_subaccount = 'ARM '::bpchar) AND (tran_mtch = 0) AND (tran_self >= 0)) OR ((tran_subledger = 2) AND (tran_subaccount = 'ARM '::bpchar) AND (tran_mtch > 0)) OR ((tran_subledger = 2) AND (tran_subaccount > 'ARM '::bpchar)) OR (tran_subledger > 2)) Total runtime: 2390290.417 ms Any suggestions/comments/ideas appreciated. -- Regards Theo