Re: Re: Using indexes for ORDER BY and PARTITION BY clause in windowing functions

Hello,

> > With this index, you will get a different plan like this,
> >
> Exactly my point, can we look at making windowing functions
> smart and make use of available indexes?

I might have guessed..

> > Does this satisfies your needs?
> >
> Not exactly. If I have missed to mention, this is not a
> production issue for me. I am trying to see if PostgreSQL
> planner produces best plans for Data Warehouse and mining
> oriented queries.

I agree to the point.

> I think Hashes can be efficiently used for sorting (and I
> believe they are used for joins too when a pre-sorted data set
> is not available via indexes). This again could my
> misinterpretation.

It is true if 'Sorting' means 'key classification without
orderings'. Hashes should always appear at inner side of a join,
I'm convinced. The "ordered' nature is not required for the case
if outer side is already ordered. If not, separate sorting will
needed.

> I lost you somewhere here. My be this is above my pay-grade :-)

Sorry for my crumsy english :-<

> Well, at least with Oracle and DB2 planners I have seen that
> the plan produced with dense_rank performs better than a series
> of nested SELECT MAX().

I see your point. Although I don't know what plans they
generates, and I don't see how to ordering and ranking without
sorting. Could you let me see what they look like?

# Nevertheless, I don't have the confidence that I can be of some
# help..

--
Kyotaro Horiguchi
NTT Open Source Software Center