Обсуждение: select single entry and its neighbours using direct-acess to index?

Is there an easy solution for this?

I'd like to select a single entry from a table and the entries that
would be previous and next given to a certain order.

like

select id from mytable where id=45 order by name,name2;

and then I'd like to select the two entries that would come before and
after according to the order "name,name2";
id is not ordered, but there is an index on  (name,name2) so the needed
infomation about previous, next should be stored somewhere in this index.

My current solution is to read all the data without the WHERE-clause and
then fetch the needed ones, which is quite time-demanding.

thnx,
peter



--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
pilsl@goldfisch.at

> select id from mytable where id=45 order by name,name2;

    Why do you want to select id if you already know it ?
    Do you not want to specify a starting value for name and name2 ?

    I'll presume you want to select a row by its 'id' and then get the
previous and next ones in the name, name2 order. I'll guess the id is
UNIQUE so these two other rows won't have the same id.

    If I guessed right I have the solution, if I'm not please explain what
you wanna do more precisely ;)

>
> and then I'd like to select the two entries that would come before and
> after according to the order "name,name2";
> id is not ordered, but there is an index on  (name,name2) so the needed
> infomation about previous, next should be stored somewhere in this index.
>
> My current solution is to read all the data without the WHERE-clause and
> then fetch the needed ones, which is quite time-demanding.
>
> thnx,
> peter
>
>
>

Pierre-Frédéric Caillaud wrote:
>
>> select id from mytable where id=45 order by name,name2;
>
>
>     Why do you want to select id if you already know it ?
>     Do you not want to specify a starting value for name and name2 ?
>
>     I'll presume you want to select a row by its 'id' and then get the
> previous and next ones in the name, name2 order. I'll guess the id is
> UNIQUE so these two other rows won't have the same id.
>
>     If I guessed right I have the solution, if I'm not please explain
> what  you wanna do more precisely ;)
>

sorry for being unclear.

but you guessed right. ID is UNIQUE and and I want to select a row by
its ID and also get the previous and next ones in the name, name2-order.

For the selected row I need all datafields and for the next and previous
I need only the ID (to have it referenced on the dataoutputpage for a
certain row).

I'm very looking forward for your solution.
thnx a lot,

peter







>>
>> and then I'd like to select the two entries that would come before
>> and  after according to the order "name,name2";
>> id is not ordered, but there is an index on  (name,name2) so the
>> needed  infomation about previous, next should be stored somewhere in
>> this index.
>>
>> My current solution is to read all the data without the WHERE-clause
>> and  then fetch the needed ones, which is quite time-demanding.
>>
>> thnx,
>> peter
>>
>>
>>
>
>


--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
pilsl@goldfisch.at

> sorry for being unclear.
>
> but you guessed right. ID is UNIQUE and and I want to select a row by
> its ID and also get the previous and next ones in the name, name2-order.
>
> For the selected row I need all datafields and for the next and previous
> I need only the ID (to have it referenced on the dataoutputpage for a
> certain row).


    OK, this is a lot clearer now.
    I suppose you have a UNIQUE(name,name2) or else, if you have several rows
with the same (name,name2) you'll get one of them, but you won't know
which one.

For example :
select * from test;
  id | name | name2
----+------+-------
   1 | a    | a
   2 | a    | b
   3 | a    | c
   4 | b    | a
   5 | b    | b
   6 | b    | c
   7 | c    | a
   8 | c    | b
   9 | c    | c
(9 lignes)

Solution #1 :

- In you application :
SELECT * FROM test WHERE id=4;
  id | name | name2
----+------+-------
   4 | b    | a

You then fetch name and name2 and issue the two following SELECT,
replacing 'a' and 'b' with name2 and name1 :

SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
name,name2 ASC LIMIT 1;
  id | name | name2
----+------+-------
   5 | b    | b

SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY
name,name2 DESC LIMIT 1;
  id | name | name2
----+------+-------
   3 | a    | c

These should use an index on (name,name2).

Solution #2 :
You could do the same in a pl/pgsql function, which will be a lot faster,
and return three rows.

It is a pity you cannot use (name,name2) > ('a','b').

On 2004-12-06, Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
wrote:
> SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
> name,name2 ASC LIMIT 1;

Write that WHERE clause instead as:

 WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a'))

This is logically equivalent, but it gives the planner a better handle on
how to use an index scan to satisfy the query.

> SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY
> name,name2 DESC LIMIT 1;

That needs to be ORDER BY name DESC, name2 DESC (the direction indicator
applies per-column and not to the output ordering). Same goes for the
WHERE clause in this query as the previous one, too.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

>> SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
>> name,name2 ASC LIMIT 1;
> Write that WHERE clause instead as:
>  WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a'))
> This is logically equivalent, but it gives the planner a better handle on
> how to use an index scan to satisfy the query.

    I thought the planner had an automatic rewriter for these situations.
It'd be interesting to see an EXPLAIN ANALYZE output to see if it's indeed
rewritten.

>> SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY
>> name,name2 DESC LIMIT 1;
> That needs to be ORDER BY name DESC, name2 DESC (the direction indicator
> applies per-column and not to the output ordering). Same goes for the
> WHERE clause in this query as the previous one, too.

    You're right, I screwed up !
    Sorry ;)

=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <lists@boutiquenumerique.com> writes:
> SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
> name,name2 ASC LIMIT 1;
>> Write that WHERE clause instead as:
>> WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a'))
>> This is logically equivalent, but it gives the planner a better handle on
>> how to use an index scan to satisfy the query.

>     I thought the planner had an automatic rewriter for these situations.

No.  There was a prior discussion of this, saying that we really ought
to support the SQL-spec row comparison syntax:
    ... WHERE (name, name2) > ('b', 'a');
which would map directly onto the semantics of a 2-column index.  We
don't have that functionality yet, though (we take the syntax but the
semantics are not SQL-compliant) let alone any ability to pass it
through to a 2-column index.

            regards, tom lane

>>     I thought the planner had an automatic rewriter for these situations.
>
> No.  There was a prior discussion of this, saying that we really ought
> to support the SQL-spec row comparison syntax:

    What I meant was that I thought the planner could rewrite :
    (A and C) or (A AND B) as A and (B or C)
    which is more index-friendly.

>     ... WHERE (name, name2) > ('b', 'a');
> which would map directly onto the semantics of a 2-column index.  We
> don't have that functionality yet, though (we take the syntax but the
> semantics are not SQL-compliant) let alone any ability to pass it
> through to a 2-column index.

    One could always use ARRAY[name,name2] > ARRAY['b','a']
    But it is NOT index-friendly...

Pierre-Frédéric Caillaud <lists@boutiquenumerique.com> writes:

>     One could always use ARRAY[name,name2] > ARRAY['b','a']
>     But it is NOT index-friendly...

It won't use an existing two-column index but you can create an expression
index on array[name,name2] and this expression will use it. It won't work if
either column is NULL though.

--
greg