Hi
I have following table:
CREATE TABLE alias (
alias_id BIGSERIAL PRIMARY KEY,
mask VARCHAR(20) NOT NULL DEFAULT '',
);
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 (20 sec. per one iteration) 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(whole program executes in 4-7s). 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