Skip site navigation (1) Skip section navigation (2)

Re: proposal: Preference SQL

From: Jan Urbański <j(dot)urbanski(at)students(dot)mimuw(dot)edu(dot)pl>
To: "Stephen R(dot) van den Berg" <srb(at)cuci(dot)nl>
Cc: Postgres - Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: Preference SQL
Date: 2008-05-31 11:33:54
Message-ID: 484137A2.9050801@students.mimuw.edu.pl (view raw or flat)
Thread:
Lists: pgsql-hackers
Stephen R. van den Berg wrote:
> Jan Urbański wrote:
>> An example of a preference query would be (quoting the linked PDF):
> 
>> SELECT * FROM programmers PREFERRING exp IN ('java', 'C++');
>> or
>> SELECT * FROM computers
>> PREFERRING HIGHEST(main_memory) AND HIGHEST(cpu_speed);
> 
> Forgive my ignorance, but it appears that this can already be achieved
> by using a properly weighted ORDER BY clause, as in:
> 
> SELECT * FROM computers
> ORDER BY HIGHEST(main_memory) DESC, HIGHEST(cpu_speed) DESC;

No, these are quite different. Consider a table with three columns: id, 
main_memory, cpu_speed containing four tuples:
   id          main_memory        cpu_speed
---------------------------------------------------
comp1             100                      80
comp2             80                      100
comp3             100                     70
comp4             60                        60

Now the result of a SELECT id FROM computers PREFERRING 
HIGHEST(main_memory) AND HIGHEST(cpu_speed) would be:
    id
---------
comp1
comp2

This is because comp1 and comp2 are incomparable under the partial order 
defined by the preferences. comp1 has the largest main memory and comp2 
the fastest CPU, but the preference states you like main memory just as 
much as CPU speed, so you get both tuples in the result. On the other 
hand, comp3 is not in the result set, because comp1 is greater than it 
under the preference partial order. The main_memory preference is 
satisfied by comp3 just as well as it is by comp1, but the cpu_speed 
preference is worse. The same goes for comp4.

And all this is significantly different from an ORDER BY, because first 
it doesn't throw away any rows and second it gives you a linear order, 
where every tuple can be compared with another. The clause you proposed 
(though it's not legal in PG, because there is no HIGHEST function, 
right?) would, as I understand it, prefer main memory more than CPU speed.

There are still some issues about the exact meaning of a PREFERRING 
clause, but it is very different from a simple ORDER BY (and it has more 
options than just PREFERRING and AND).
Anyway, from what I've read most or all preference clauses can be 
rewritten to standard clauses, but sometimes it's difficult, and many 
times it's costly.

Cheers,
Jan

-- 
Jan Urbanski
GPG key ID: E583D7D2

ouden estin


In response to

pgsql-hackers by date

Next:From: coutinhoDate: 2008-05-31 13:20:54
Subject: Re: Packages in oracle Style
Previous:From: Stephen R. van den BergDate: 2008-05-31 09:04:42
Subject: Re: proposal: Preference SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group