Thread: 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
Re: select single entry and its neighbours using direct-acess to index?
From
Pierre-Frédéric Caillaud
Date:
> 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
Re: select single entry and its neighbours using direct-acess to index?
From
Pierre-Frédéric Caillaud
Date:
> 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').
Re: select single entry and its neighbours using direct-acess to index?
From
Andrew - Supernews
Date:
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
Re: select single entry and its neighbours using direct-acess to index?
From
Pierre-Frédéric Caillaud
Date:
>> 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
Re: select single entry and its neighbours using direct-acess to index?
From
Pierre-Frédéric Caillaud
Date:
>> 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