Hitoshi Harada wrote:
> >> > Test 3 and 5 did not seem to make use of an index to get a sorted
> list
> >> of
> >> > results. I disabled enable_seqscan but the planner still failed to
> >> choose
> >> > index_scan. Is there any reason for this? Perhaps I'm missing
> something.
> >> > Hitoshi, can you take a look at this?
> >
> >> Ah, good point. Maybe it's because I haven't paid attention to choose
> >> index_scan for upper sort node. I just put the sort node whatever the
> >> downer node is, so it might be needed to sink the information down to
> >> scan choice process that we use sort node upper. Could someone point
> >> me out how to do it, or which part of the existing code would be a
> >> good guide?
> >
> > I know you need to wait for an answer about this, so I'd like to delay
> any
> > further performance tests until that's sorted out as it should affect
> > performance of larger tables quite a bit.
> >
>
> I found how to do it, though it's only on the case you gave. Thinking
> about the planner optimization of the Window nodes (and its attached
> Sort nodes), we must consider the execution order of more than one
> node. In the test case we only take care of only one window, but there
> may be more window/sort node sets, which is too difficult to choose
> the best execution order including the downer indexscan, mergejoin in
> subquery and sort-based GROUP BY. So I didn't touch the complicated
> planner jungle. I rewrote the patch so that only the given bottom
> window's sort can consider indexscan. Deeper optimizations are over my
> capability.
I've just looked into what some other implementations do. Sybase seems to do
exactly what you've done. It only looks at the first window clause in the
query. Oracle seems to use the index regardless to the position of the
window clause. To me personally what you've done seems fine for now. Perhaps
something could be done later to improve on this. Maybe someone else has
ideas about how to do it?
It seems quite similar to SELECT MAX(idxcol),MAX(idxcol2) where the planner
often makes use of 2 indexes when available, yet this case is probably far
more simple as there is always just 1 row. Costing would likely be more
complex with the windowing functions version.
Good work.
I'll continue with more benchmarks soon.
David.