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
