Re: Cursor-based results: bafflingly slow

Oliver Jowett <oliver@opencloud.com> writes:
> If you want to stream results promptly, you may be stuck with removing
> that ORDER BY. I'm not sure if there's any way to say to the planner
> "give me a plan with a low startup cost, even if it has a higher total
> cost" which seems to be what you want here.

You need to use a cursor for that.  The problem with the fetchsize
mechanism is that it doesn't give the planner any clue that the user
might not intend to fetch the whole result, or might prefer low startup
cost to low total cost anyway.

> I came across some (very old) posts in the archives that suggested that
> DECLARE CURSOR has a fudge where it ranks plan costs assuming that only
> some rows (10%?) will actually be fetched. I'm not sure if that's true
> of the current planner.

Exactly.  As of 8.4 you can control the percentage, too.

            regards, tom lane