Thread: An Index Scanning Solution question
Hi all! Earlier I sent a question about multi-order index scanning (http://archives.postgresql.org/pgsql-sql/2004-04/msg00276.php). I can solve the problem with own OPERATOR CLASSes. But this solution increase number of my indexes exponentially. In my applicaton I have already more then 200 indexes on a table with 500 000 rows. I will have 1 000 000 records at the end of this year. I'd like to ask why the index scaning can't move on an index in multi-order directions (For exapmle: 1.column: forward, 2.column: backward and 3.column: forward again)? So I wouldn't have to use so many indexes. Has somebody tried to implement this idea in Postgres or is there a more difficult reason in the postgres implementation which cause this defect? Thank you in anticipation! Antal Attila
On Wed, May 19, 2004 at 15:17:01 +0200, Atesz <atesz@ritek.hu> wrote: > > I'd like to ask why the index scaning can't move on an index in > multi-order directions (For exapmle: 1.column: forward, 2.column: > backward and 3.column: forward again)? So I wouldn't have to use so many > indexes. Has somebody tried to implement this idea in Postgres or is > there a more difficult reason in the postgres implementation which > cause this defect? Because there is only one order on an index. So you can only go forward and backwards over all of the columns/functions. While it would be nice if indexes could be declared with some columns ascending and others descending, this wouldn't solve your problem. I would be surprized if all those combinations of indexes were really needed. In many cases adding extra columns to an index doesn't buy you much. Also I wouldn't expect to generate data in multiple orders for the same set of data and that in practice the number of indexes you need would be bounded by the number of different reports / common queries you have.
On Thu, 20 May 2004, Bruno Wolff III wrote: > On Wed, May 19, 2004 at 15:17:01 +0200, > Atesz <atesz@ritek.hu> wrote: > > > > I'd like to ask why the index scaning can't move on an index in > > multi-order directions (For exapmle: 1.column: forward, 2.column: > > backward and 3.column: forward again)? So I wouldn't have to use so many > > indexes. Has somebody tried to implement this idea in Postgres or is > > there a more difficult reason in the postgres implementation which > > cause this defect? > > Because there is only one order on an index. So you can only go forward > and backwards over all of the columns/functions. If you're willing to make multiple visits you might be able to scan past and back but I don't know how that'd work for our indexes.