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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
