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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Import Notes
Reply to msg id not found: 20060707113035.GA2400@dagan.sigpipe.cz
# 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