SEO

#1 (**permalink**) 04-09-2008, 10:17 AM

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

#2 (**permalink**) 04-09-2008, 10:17 AM

> 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 '',
);

#3 (**permalink**) 04-09-2008, 10:19 AM

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/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEmkSQIB7bNG8LQkwRAs2pAJ4hwD/FpCtPLhxiPhAnzsS+Gps+KwCdHLu+
9QSZeF+D5BRh2NZ16WMik8c=
=D83T
-----END PGP SIGNATURE-----

#4 (**permalink**) 04-09-2008, 10:31 AM

# 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

---------------------------(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

#5 (**permalink**) 04-09-2008, 10:31 AM

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/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFErkLAIB7bNG8LQkwRArCVAJ4lbOKyN8djdmEedkZKB1 TWVgEudQCdG2bq
1xxMTmchUbcjo0pQHp1+RUc=
=WHaT
-----END PGP SIGNATURE-----

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Why my cursor construction is so slow?