Обсуждение: Problem with result ordering
Hi, when I fire the following query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); I will get the same results in the same order, as in in the next query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (11042,42197,672089,26250,16279); I wonder, how it is possible, to retrieve the results in the same order, as queried in the list. The listed IDs are from an application outside the database. Version is PostgreSQL 8.2.1 Has anyone an idea, how to do this, while PostgreSQL knows nothing about hints, like oracle does? THX, Thorsten
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/25/07 09:45, Thorsten Körner wrote: > Hi, > > when I fire the following query: > select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, > 11042, 16279, 42197, 672089); > > I will get the same results in the same order, as in in the next query: > select m_id, m_u_id, m_title, m_rating from tablename where m_id in > (11042,42197,672089,26250,16279); > > I wonder, how it is possible, to retrieve the results in the same order, as > queried in the list. The listed IDs are from an application outside the > database. > > Version is PostgreSQL 8.2.1 > > Has anyone an idea, how to do this, while PostgreSQL knows nothing about > hints, like oracle does? What do you mean "same order"? The order that they are listed in the IN() clause? I doubt it. SQL is, by definition, set-oriented and the only ways to guarantee a certain output sequence are ORDER BY and GROUP BY, and they use collating sequences. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFuNLmS9HxQb37XmcRAmTSAJ9mbcf8AptR4YsjdG7xBocasldfdgCdEGSz MNjSxmx3KBP79LXRzTgQ2Qk= =nif4 -----END PGP SIGNATURE-----
Thorsten =?iso-8859-1?q?K=F6rner?= <t.koerner@cappuccinosoft.de> writes:
> select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250,
> 11042, 16279, 42197, 672089);
> I wonder, how it is possible, to retrieve the results in the same order, as
> queried in the list.
You could rewrite the query as
select ... from tablename where m_id = 26250
union all
select ... from tablename where m_id = 11042
union all
select ... from tablename where m_id = 16279
union all
select ... from tablename where m_id = 42197
union all
select ... from tablename where m_id = 672089
This isn't guaranteed by the SQL spec to produce the results in any
particular order either; but there's no good reason for PG to rearrange
the order of the UNION arms, whereas there are plenty of good reasons to
try to optimize fetching of individual rows.
regards, tom lane
am Thu, dem 25.01.2007, um 16:45:23 +0100 mailte Thorsten Körner folgendes: > Hi, > > when I fire the following query: > select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, > 11042, 16279, 42197, 672089); > > I will get the same results in the same order, as in in the next query: > select m_id, m_u_id, m_title, m_rating from tablename where m_id in > (11042,42197,672089,26250,16279); > > I wonder, how it is possible, to retrieve the results in the same order, as > queried in the list. The listed IDs are from an application outside the > database. a little trick; store your order-definition and the where-condition in a separate table. Our table: test=*# select * from foo; id | val ----+------- 1 | text1 2 | text2 3 | text3 4 | text4 5 | text5 (5 rows) Our sort-order: -- col a: the sort-order -- col b: the where-condition test=*# select * from o; a | b ---+--- 1 | 3 2 | 5 3 | 1 (3 rows) test=*# select foo.id, foo.val from foo join o on foo.id=o.b order by o.a; id | val ----+------- 3 | text3 5 | text5 1 | text1 (3 rows) You can try it without this table, only with generate_series or so. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hi Fillip, thanks for your hint, I have tested it on a development database, and it worked well. Are there any experiences how this will affect performance on a large database, with very high traffic? Is it recommended to use temp tables in such an environment? THX in advance Thorsten Am Donnerstag, 25. Januar 2007 17:02 schrieb Filip Rembiałkowski: > 2007/1/25, Thorsten Körner <t.koerner@cappuccinosoft.de>: > > Hi, > > > > when I fire the following query: > > select m_id, m_u_id, m_title, m_rating from tablename where m_id in > > (26250, 11042, 16279, 42197, 672089); > > > > I will get the same results in the same order, as in in the next query: > > select m_id, m_u_id, m_title, m_rating from tablename where m_id in > > (11042,42197,672089,26250,16279); > > > > I wonder, how it is possible, to retrieve the results in the same order, > > as queried in the list. The listed IDs are from an application outside > > the database. > > > > Version is PostgreSQL 8.2.1 > > > > Has anyone an idea, how to do this, while PostgreSQL knows nothing about > > hints, like oracle does? > > obvious solution is to create temporary table like > create temp table tmp ( id serial, key integer ); > then populate it with your list in order, > and then join it with your source table. > > but it will require some extra coding, either in your app or in PL > set-returning function > > F. -- CappuccinoSoft Business Systems Hamburg
The question I'd ask before offering a solution is, "Does the order of the id data matter, or is it a question of having all the results for a given id together before proceeding to the next id?" The answer to this will determine whether or not adding either a group by clause or an order by clause will help. Is there a reason you client app doesn't submit a simple select for each of the required ids? You'd have to do some checking to see whether it pays to have the ordering or grouping operation handled on the server or client. Other options to consider, perhaps affecting performance and security, would be parameterized queries or stored procedures. Much depends on the design and implementation of your client app. I know, e.g., that in ASP.NET 2, and later, you can handle multiple resultsets from a single datasource, so a trivially simple SQL script that consists of the simplest SELECT statements might be a viable option. But it is hard to advise since you don't say if you have access to or control over the source code for the client app or what it is written in. In my experience, I always have to run some benchmarks for a given distributed application to figure out how best to distribute the workload, and there are always plenty of different ways to do things, with often big differences in performance and security. It seems never to be trivial to figure this out without some testing before a final decision. I can never just assume that it is best to do all the processing in the RDBMS backend to my apps. HTH Ted ----- Original Message ----- From: "Thorsten Körner" <t.koerner@cappuccinosoft.de> To: <pgsql-general@postgresql.org> Sent: Thursday, January 25, 2007 10:45 AM Subject: [GENERAL] Problem with result ordering > Hi, > > when I fire the following query: > select m_id, m_u_id, m_title, m_rating from tablename where m_id in > (26250, > 11042, 16279, 42197, 672089); > > I will get the same results in the same order, as in in the next query: > select m_id, m_u_id, m_title, m_rating from tablename where m_id in > (11042,42197,672089,26250,16279); > > I wonder, how it is possible, to retrieve the results in the same order, > as > queried in the list. The listed IDs are from an application outside the > database. > > Version is PostgreSQL 8.2.1 > > Has anyone an idea, how to do this, while PostgreSQL knows nothing about > hints, like oracle does? > > THX, > Thorsten > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Tom Lane wrote: > Thorsten =?iso-8859-1?q?K=F6rner?= <t.koerner@cappuccinosoft.de> writes: > >> select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, >> 11042, 16279, 42197, 672089); >> > You could rewrite the query as > > select ... from tablename where m_id = 26250 > union all > select ... from tablename where m_id = 11042 > union all > select ... from tablename where m_id = 16279 > union all > select ... from tablename where m_id = 42197 > union all > select ... from tablename where m_id = 672089 > > This isn't guaranteed by the SQL spec to produce the results in any > particular order either; but there's no good reason for PG to rearrange > the order of the UNION arms, whereas there are plenty of good reasons to > try to optimize fetching of individual rows. > Or a variant of this, SELECT m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089) ORDER BY m_id=26250, m_id=11042, m_id=16279, m_id=42197, m_id=672089; -- Tommy Gildseth http://www.gildseth.com/
Hi Ted, Am Donnerstag, 25. Januar 2007 19:53 schrieb Ted Byers: > The question I'd ask before offering a solution is, "Does the order of the > id data matter, or is it a question of having all the results for a given > id together before proceeding to the next id?" The answer to this will > determine whether or not adding either a group by clause or an order by > clause will help. > > Is there a reason you client app doesn't submit a simple select for each of > the required ids? You'd have to do some checking to see whether it pays to > have the ordering or grouping operation handled on the server or client. > Other options to consider, perhaps affecting performance and security, > would be parameterized queries or stored procedures. Yes, the reason is, that a large list of ids are generated from a users request, coming from outside our application (for example as an EXCEL sheet), and the output msut be ordered by the ordering in this list. Surely we can handle this in our client application (java code), but I think it would be more sexy to have this possibility in the database logic, since our client-application should not do much more than retrieve data from the db and then present it. Thanks for your comments Thorsten -- CappuccinoSoft Business Systems Hamburg
> Hi Ted, > Hi Thorsten, > Am Donnerstag, 25. Januar 2007 19:53 schrieb Ted Byers: >> The question I'd ask before offering a solution is, "Does the order of >> the >> id data matter, or is it a question of having all the results for a given >> id together before proceeding to the next id?" The answer to this will >> determine whether or not adding either a group by clause or an order by >> clause will help. >> >> Is there a reason you client app doesn't submit a simple select for each >> of >> the required ids? You'd have to do some checking to see whether it pays >> to >> have the ordering or grouping operation handled on the server or client. >> Other options to consider, perhaps affecting performance and security, >> would be parameterized queries or stored procedures. > > Yes, the reason is, that a large list of ids are generated from a users > request, coming from outside our application (for example as an EXCEL > sheet), > and the output msut be ordered by the ordering in this list. > Surely we can handle this in our client application (java code), but I > think > it would be more sexy to have this possibility in the database logic, > since > our client-application should not do much more than retrieve data from the > db > and then present it. > To be honest, your rationale here makes no sense to me, business or otherwise. I think I'd be remiss if I didn't tell you this. Of course, what you do is up to you, but I never concern myself with what is 'more sexy' when designing a distributed application. I can see a number of situations in which your approach would result in terrible performance. If you have a lot of users, and you're putting all the workload on your data server, all the client apps will end up spending a lot of time waiting for the server to do its thing. Ordering the display of data, while it can be helped by the database, is really a presentation issue and IMHO the best place for that, by default, is the client app (i.e. do it server side only if there is a very good reason to do so). If I understand you correctly, you have a java application as the client app, and it receives your users' ID data, from whatever source. I'd assume, and hope, that you have built code to read the IDs from sources like your Excel spreadsheet, but that doesn't matter that much. For the purpose of this exchange, it wouldn't matter if you made your clients enter the data manually (though IMHO it would be sadistic to make users manually enter a long list of values when you can as easily have the program read them from a file of whatever format). The point it that you say "our client-application should not do much more than retrieve data from the db and then present it", and this implies you get the IDs into your client application. You say you're getting "a large list of ids" "coming from outside our application." If your database is large, and your list of IDs is long, you may be taking a huge performance hit by making the database perform either an ordering or grouping that both would be unnecessary if you constructed a series of simple parameterized queries in your client app and executed them in the order you desire. Whether or not this matters will depend on just how large your large is, how many simultaneous users there'd be, and how powerful the server hardware is (but I'd be quite upset if one of my developers made me pay more for a faster server just because he or she thought it would be sexy to do all the work on the server rather than the client). Given what you've just said, if I were in your place, I'd be addressing the ordering issues in the client java application first, and then look at doing it in the database only if doing this work in the client app presented problems that could be addressed by doing the work on the server. Equally importantly, if there is a reason to not take the obvious and simple option, I'd be doing some performance evaluation based on enough test data to reasonably simulate real world usage so that I'd have hard data on which option is to be preferred. > Thanks for your comments > Thorsten > You're welcome. I hope you find this useful. Ted > -- > CappuccinoSoft Business Systems > Hamburg > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >
2007/1/25, Thorsten Körner <t.koerner@cappuccinosoft.de>: > Hi, > > when I fire the following query: > select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, > 11042, 16279, 42197, 672089); > > I will get the same results in the same order, as in in the next query: > select m_id, m_u_id, m_title, m_rating from tablename where m_id in > (11042,42197,672089,26250,16279); > > I wonder, how it is possible, to retrieve the results in the same order, as > queried in the list. The listed IDs are from an application outside the > database. > > Version is PostgreSQL 8.2.1 > > Has anyone an idea, how to do this, while PostgreSQL knows nothing about > hints, like oracle does? obvious solution is to create temporary table like create temp table tmp ( id serial, key integer ); then populate it with your list in order, and then join it with your source table. but it will require some extra coding, either in your app or in PL set-returning function F.