Re: Why my cursor construction is so slow?

On Tue, Jun 20, 2006 at 02:06:19AM -0700, biuro(at)globeinphotos(dot)com wrote:
> Such construction is very slow but when I modify SQL to:
> OPEN cursor1 FOR SELECT * FROM alias WHERE mask>=alias_out
> ORDER BY mask LIMIT 100;
>
> it works very fast. It is strange for me becuase I've understood so far
> that when cursor is open select is executed but Postgres does not
> select all rows - only cursor is positioned on first row, when you
> execute fetch next row is read. But this example shows something
> different.

PostgreSQL tries to optimise for overall query time. Without the limit
it tries to find a plan that will return the whole set as quick as
possible. With the LIMIT it might take a different approach, which
might be worse if you read the whole lot, but better for a limited set.
A fast-start plan so to speak.

To see detail I'd suggest doing an EXPLAIN ANALYZE over the query with
and with limit to see the changes.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.