Thread: Curson prbolem
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
biuro@globeinphotos.com writes: > [slow:] > OPEN cursor1 FOR SELECT * FROM alias WHERE mask>=alias_out > ORDER BY mask; > [fast:] > OPEN cursor1 FOR SELECT * FROM alias WHERE mask>=alias_out > ORDER BY mask LIMIT 100; The difference is that in the first case the planner has to assume you intend to fetch all the rows with mask>=something (and I'll bet the something is a plpgsql variable, so the planner can't even see its value). In this case a sort-based plan looks like a winner. In the second case, since you only need to fetch 100 rows, it's clearly best to scan the index beginning at mask = alias_out. > Can somebody clarify what is wrong with my example? I need select > without LIMIT 100 part. Why? You should always tell the SQL engine what it is that you really want --- leaving it in the dark about your intentions is a good way to destroy performance, as you are finding out. If I were you I would get rid of the row-counting inside the loop entirely, and use the "LIMIT n" clause to handle that. regards, tom lane