Thread: Why my cursor construction is so slow?

Hi
I have following table:

CREATE OR REPLACE FUNCTION alias(
                   v_mask         alias.mask%TYPE,
                   ) RETURNS INT8 AS
with index:
CREATE INDEX alias_mask_ind ON alias(mask);

and this table has about 1 million rows.

In DB procedure I execute:

    LOOP
       <........>
        OPEN cursor1 FOR SELECT * FROM alias WHERE mask>=alias_out
ORDER BY mask;
        i:=0;
        LOOP
           i:=i+1;
             FETCH cursor1 INTO alias_row;
           EXIT WHEN i=10;
        END LOOP;
      CLOSE cursor1;
     EXIT WHEN end_number=10000;
    END LOOP;

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.

Can somebody clarify what is wrong with my example? I need select
without LIMIT 100 part.

Regards
Michal Szymanski
http://blog.szymanskich.net

> CREATE OR REPLACE FUNCTION alias(
>                    v_mask         alias.mask%TYPE,
>                    ) RETURNS INT8 AS

Sorry my mistake it should be:

CREATE TABLE  alias (
       alias_id           BIGSERIAL PRIMARY KEY,
       mask                      VARCHAR(20) NOT NULL DEFAULT '',
);

On Tue, Jun 20, 2006 at 02:06:19AM -0700, biuro@globeinphotos.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@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

# kleptog@svana.org / 2006-06-22 09:19:44 +0200:
> On Tue, Jun 20, 2006 at 02:06:19AM -0700, biuro@globeinphotos.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.

    That looks like the wrong approach for a cursor.

> 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.

    That looks like a better approach for a cursor.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

On Fri, Jul 07, 2006 at 11:30:35AM +0000, Roman Neuhauser wrote:
> > 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.
>
>     That looks like a better approach for a cursor.

For a cursor postgres assumes you're going to ask for about 10% of the
result, so it does aim for a reasonably fast-start plan. It probably
depends on the specifics of the situation how well it works...

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