Hi,
Thanks for reply, As you have mentioned I need to get row numbers for my query, so when I make some other query with same data I will know which row number has a particular ID. As you mentioned "You can
do this with a temporary sequence, among other approaches...". Can you point me to some sources or give me some examples, please?

Thanks

Trigve

----- Original Message ----
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Sent: Wednesday, May 30, 2007 6:45:53 PM
Subject: Re: [SQL] slow query execution

On Wed, May 30, 2007 at 08:56:45AM -0700, Trigve Siver wrote:
> Hi all,
>
> This query executes very slow:
>
> select (select count(*) from customer where id <= a.id) as row, id,
> from customer as a order by id;

So you are trying to get the ordinal position of every ID in the
table? I'm not surprised it takes a long time -- you have to join
the whole table to itself and then do a lot of counting. Are you
just trying to get the "row number" for your query answer? You can
do this with a temporary sequence, among other approaches, more
cheaply.

A


--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq






__________________________________________________ __________________________________Yahoo! oneSearch: Finally, mobile search
that gives answers, not web links.
http://mobile.yahoo.com/mobileweb/on...h?refer=1ONXIC

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 5/30/07, Trigve Siver <trigves@yahoo.com> wrote:
> Can you point me to some sources
> or give me some examples, please?

CREATE OR REPLACE FUNCTION ROWNUM() RETURNS BIGINT AS
$$
BEGIN
RETURN NEXTVAL('ROWNUM_SEQ');
EXCEPTION WHEN OTHERS THEN
CREATE TEMP SEQUENCE ROWNUM_SEQ;
RETURN NEXTVAL('ROWNUM_SEQ');
END;
$$
LANGUAGE 'PLPGSQL';

SELECT ROWNUM(), S.X
FROM GENERATE_SERIES(5,1,-1) S(X);

Remember to reset the sequence value if you use this more than once in
the same session.

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

On Wed, May 30, 2007 at 10:03:16AM -0700, Trigve Siver wrote:
> Hi, Thanks for reply, As you have mentioned I need to get row
> numbers for my query, so when I make some other query with same
> data I will know which row number has a particular ID.

Oh, wait. If _that's_ your plan, then this will never work. The
data could change, and your row numbers would come out wrong.
What do you need "row numbers" for anyway? The very idea is inimical
to SQL, because the data is fundamentally unordered.

> As you
> mentioned "You can do this with a temporary sequence, among other
> approaches...". Can you point me to some sources or give me some
> examples, please?

BEGIN;
CREATE SEQUENCE temp_seq;
SELECT nextval('temp_seq'), other stuff from table;
DROP SEQUENCE temp_seq;
COMMIT/ROLLBACK;

If you only select, you don't have to do the DROP, you just ROLLBACK.

I think there's some nifty way to get generate_series to do this too,
but I don't know it offhand (generating row numbers sounds to me like
a bad idea, so I don't do it).

A

--
Andrew Sullivan | ajs@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster