Surprising (?) Sequence Behavior

From: "Richard M(dot) Kues" <software(at)riva(dot)at>
To: pgsql-general(at)postgresql(dot)org
Subject: Surprising (?) Sequence Behavior
Date: 2008-01-28 19:38:38
Message-ID: 479E2F3E.7060003@riva.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hallo all

During a performance tuning session I had a complex
query that gives some form of ranking. The "correct" way to
solve this, is the use of a scalar subquery that provides
the rank (or use "dense_rank over" in oracle).

But in my case the query is much too slow in this special case.
Even with small number of records that fit into memory (no IO).

So I'am searching for a faster solution and tried
also to use temporary sequences to achieve the same effect.

Example 1:
DROP SEQUENCE IF EXISTS s;
CREATE TEMPORARY SEQUENCE s;

SELECT
nextval('s'), t.name
FROM
(
SELECT
tablename AS name
FROM
pg_tables
ORDER BY
tablename
) AS t;

gives:
1 pg_aggregate
2 pg_am
3 pg_amop
4 pg_amproc
5 pg_attrdef
6 pg_attribute
7 pg_auth_members

But if this query is combined with a simple extension it does
not work as expected.

DROP SEQUENCE IF EXISTS s;
CREATE TEMPORARY SEQUENCE s;

SELECT
nextval('s'), t.name
FROM
(
SELECT
tablename AS name
FROM
pg_tables
ORDER BY
tablename
) AS t
WHERE
t.name = 'pg_am'
;

The result is:
1 pg_am

instead of:
2 pg_am

At least for me this is surprising!
Any hints? Or do I miss something obvious?

thanks a lot, richard

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Sabino Mullane 2008-01-28 20:15:54 Re: [GENERAL] SHA1 on postgres 8.3
Previous Message Douglas McNaught 2008-01-28 19:28:37 Re: close connection