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



Wed, 19 May 2004 15:17:01 +0200
"Atesz" <atesz@ritek.hu>
An Index Scanning Solution question
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.

Thu, 20 May 2004 15:38:05 -0500
Bruno Wolff III <bruno@wolff.to>
Re: An Index Scanning Solution question

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.

Thu, 20 May 2004 14:18:49 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: An Index Scanning Solution question