Multicolumn order by

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux