Обсуждение: Surprising (?) Sequence Behavior

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

"Richard M. Kues" <software@riva.at> writes:
> 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!

Why do you find it surprising?  Per spec, the SELECT output list is not
evaluated at rows that fail the WHERE clause.  This must be so; consider
examples like
    SELECT 1/x FROM t WHERE x <> 0;

I think what you need is three levels of nested SELECT, with the
nextval() done in the middle level, and probably an "OFFSET 0" in the
middle one to keep Postgres from collapsing the top and middle together.

            regards, tom lane

Tom Lane schrieb:

>
>> At least for me this is surprising!
>
> Why do you find it surprising?  Per spec, the SELECT output list is not
> evaluated at rows that fail the WHERE clause.  This must be so; consider
> examples like
>     SELECT 1/x FROM t WHERE x <> 0;

Of ocurse!

>
> I think what you need is three levels of nested SELECT, with the
> nextval() done in the middle level, and probably an "OFFSET 0" in the
> middle one to keep Postgres from collapsing the top and middle together.
>

For the archves.
This works:

DROP SEQUENCE IF EXISTS s;
CREATE TEMPORARY SEQUENCE s;

SELECT * FROM
(
    SELECT
       nextval('s') AS num,
       tablename AS name
    FROM
    (
       SELECT
          tablename
       FROM
          pg_tables
       ORDER BY tablename
    ) AS t
    OFFSET 0
) AS ranked
WHERE
    ranked.name = 'pg_am'


Thanks a lot!
richard