Why my cursor construction is so slow? - Mailing list pgsql-general

From biuro@globeinphotos.com
Subject Why my cursor construction is so slow?
Date
Msg-id 1150794379.420950.259850@u72g2000cwu.googlegroups.com
Whole thread Raw
Responses Re: Why my cursor construction is so slow?
Re: Why my cursor construction is so slow?
List pgsql-general
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


pgsql-general by date:

Previous
From: "Chris Golden"
Date:
Subject: Interface Guidance and Opinions Needed
Next
From: "Szymic1"
Date:
Subject: Re: Why my cursor construction is so slow?