Ergebnis beziehen sobald die erste Bedingung zutrifft.

Hallo,
ich möchte aus einer Tabelle mit Titeln in verschiedenen Sprachen den ersten Treffer bei Abfrage nach: zuerst dem deutschen Titel, dann dem englischen Titel und wenn keiner zutrifft irgendein Titel.

Die Tabelle sieht wie folgt aus:

id (pk) | item_id | language_id
--------*---------*--------------
1       |1        |english
2       |1        |deutsch
3       |1        |japanisch
4       |2        |spanisch
5       |2        |japanisch

Im Moment frage ich alle Sprachen ab und treffe die Auswahl mittels php. Bei vielen Sprachen dauern die Abfragen entsprechend. Lässt sich das irgendwie performanter mittels coalesce machen? Oder lässt sich das Resultat von der Datenbank nach einem Array mit Sprachen sortieren den ich mit der Anfrage übergebe (alias multisort in php)?

Gruß,
Strippenzieher

Etwas in der Art:

select id, 
       item_id, 
       language_id
from (
  select the_table.*, 
         row_number() over (partition by item_id order by case when 'deutsch' then 1 when 'english' then 2 else 3 end) as rn
  from the_table
) t
where rn = 1

Danke für die schnelle Antwort.

Jedoch erhalte ich die Fehlermeldung: ungültige Eingabesyntax für Typ boolean: »deutsch«
und aus PostgreSQL: Documentation: 8.4: Window Functions werde ich nicht wirklich schlau. Gibt es vielleicht eine verständlichere Wissenquelle im Netz - wenn möglich mit Beispielen?

Und ist es richtig, dass der Name der Spalte mit den Sprachen nicht in der Query auftaucht?

Gruß,
Strippenzieher

Sorry, muss natürlich

order by case when language_id =  'deutsch' then 1 when language_id =  'english' then 2 else 3 end

sein

Und ist es richtig, dass der Name der Spalte mit den Sprachen nicht in der Query auftaucht?

Nein, natürlich nicht. Aber mit ein bisschen Nachdenken hättest schon drauf kommen können welche Bedingung da fehlt.

Neben der Antwort von castorp: prüfe mal bitte, ob da ein Index beschleunigend wirkt, z.B. sowas:

test=# create table strippenzieher (id serial primary key, item_id int, language_id text);
NOTICE:  CREATE TABLE will create implicit sequence "strippenzieher_id_seq" for serial column "strippenzieher.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "strippenzieher_pkey" for table "strippenzieher"
CREATE TABLE
test=*# create index idx1 on strippenzieher(case when language_id='deutsch' then 1 when language_id='english' then 2 else 3 end);
ERROR:  syntax error at or near "case"
LINE 1: create index idx1 on strippenzieher(case when language_id='d...
                                            ^
test=*# create index idx1 on strippenzieher((case when language_id='deutsch' then 1 when language_id='english' then 2 else 3 end));
CREATE INDEX
test=*# copy strippenzieher from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1    1       english
>> 2    1       deutsch
>> 3    1       japanisch
>> 4    2       spanisch
>> 5    2       japanisch
>> \.
test=*# commit;
COMMIT
test=# select * from strippenzieher order by (case when language_id='deutsch' then 1 when language_id='english' then 2 else 3 end);
 id | item_id | language_id
----+---------+-------------
  2 |       1 | deutsch
  1 |       1 | english
  5 |       2 | japanisch
  4 |       2 | spanisch
  3 |       1 | japanisch
(5 rows)

test=*# explain select * from strippenzieher order by (case when language_id='deutsch' then 1 when language_id='english' then 2 else 3 end);
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using idx1 on strippenzieher  (cost=0.00..71.45 rows=1160 width=40)
(1 row)

Ich war selber etwas baff, daß im explain der Index gleich genutzt wird, habe aber keine ausreichende Datenbasis, das mal effektiv zu testen. Würde mich aber interessieren.


Andreas

Bei einer Tabelle mit ca. 1 Mio Zeilen und Index:

explain analyse select * from authornames order by (case when language='ger' then 1 when language='eng' then 2 else 3 end);
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_windowfct on authornames  (cost=0.00..58087.65 rows=909965 width=99) (actual time=0.067..917.577 rows=909965 loops=1)
 Total runtime: 1499.293 ms
(2 Zeilen)

Ohne Index:

explain analyse select * from authornames order by (case when language='ger' then 1 when language='eng' then 2 else 3 end);
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=160955.82..163230.73 rows=909965 width=99) (actual time=1804.458..2539.665 rows=909965 loops=1)
   Sort Key: (CASE WHEN ((language)::text = 'ger'::text) THEN 1 WHEN ((language)::text = 'eng'::text) THEN 2 ELSE 3 END)
   Sort Method:  external merge  Disk: 52240kB
   ->  Seq Scan on authornames  (cost=0.00..21123.48 rows=909965 width=99) (actual time=0.008..819.792 rows=909965 loops=1)
 Total runtime: 3201.395 ms
(5 Zeilen)

Bei einer Tabelle mit knapp 11 Mio. Zeilen wird der index eigenartigerweise nicht benutzt:

create index idx_editions_windowfct on editions((case when languageid='ger' then 1 when languageid='eng' then 2 else 3 end));
CREATE INDEX
explain select * from editions order by (case when languageid='ger' then 1 when languageid='eng' then 2 else 3 end);
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Sort  (cost=2771115.06..2797850.86 rows=10694321 width=102)
   Sort Key: (CASE WHEN (languageid = 'ger'::bpchar) THEN 1 WHEN (languageid = 'eng'::bpchar) THEN 2 ELSE 3 END)
   ->  Seq Scan on editions  (cost=0.00..352841.82 rows=10694321 width=102)
(3 Zeilen)

Auch ein vacuum analyse änderte daran nichts. Eventuell aber der Fakt, dass in ersterer Tabelle fast ausschließlich englischsprachige Einträge sind, in letzterer die Sprachzusammensetzung sehr viel gemischter ist (wobei das meinem Verständnis nach erst recht ein Grund wäre den Index zu nutzen).

Danke.

Ja, komisch. Mal probiert, seq-scans abzuschalten?

Ähm, warum einklich keine IDs für die Sprache, INT-Werte lassen sich schon besser vergleichen als Strings…


Andreas

Das kann man so pauschal nicht sagen:

http://www.depesz.com/2012/06/07/123-vs-depesz-what-is-faster/
http://www.depesz.com/2012/06/08/123″-vs-depesz-followup/

explain analyse mit SET enable_seqscan TO on;

explain analyse select * from editions order by (case when languageid='ger' then 1 when languageid='eng' then 2 else 3 end);
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2771115.06..2797850.86 rows=10694321 width=102) (actual time=33336.618..44772.769 rows=10696230 loops=1)
   Sort Key: (CASE WHEN (languageid = 'ger'::bpchar) THEN 1 WHEN (languageid = 'eng'::bpchar) THEN 2 ELSE 3 END)
   Sort Method:  external merge  Disk: 1049104kB
   ->  Seq Scan on editions  (cost=0.00..352841.82 rows=10694321 width=102) (actual time=0.020..11400.019 rows=10696230 loops=1)
 Total runtime: 53230.327 ms
(5 Zeilen)

explain analyse mit SET enable_seqscan TO off;

explain analyse select * from editions order by (case when languageid='ger' then 1 when languageid='eng' then 2 else 3 end);
                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_editions_windowfct on editions  (cost=0.00..3986118.95 rows=10694321 width=102) (actual time=0.079..16175.002 rows=10696230 loops=1)
 Total runtime: 23912.541 ms
(2 Zeilen)

Es lohnt sich also in der Tat - da ich jedoch wechselnde Sprachkombinationen und Reihenfolge habe bezweifle ich, dass es sich lohnt etliche Indexe zu erstellen.

Wegen der Verwendung von Strings für die Sprachen - es stand auf meiner ToDo-Liste das zu ändern. Nun eigentlich nicht mehr. Ich verwende zudem lediglich dreistellige Spachcodes in der Tabelle und müsste diese gegen dreistellige INT-Werte tauschen. Das wird sich wohl kaum lohnen. Vielen Dank also für den aufschlussreichen Link und die Hilfe!

Strippenzieher

Hallo,
irgendwie stimmt die Performance noch nicht. Mache ich vielleicht etwas grundlegend falsch?

explain analyse                                                 
SELECT ba.bookid, authorid, title, languageid
,row_number() OVER (
PARTITION BY e.bookid ORDER BY CASE 
WHEN languageid = '123' THEN 1
WHEN languageid = '401' THEN 2
WHEN languageid = '150' THEN 3
ELSE 4
END
) AS rn
FROM editions e
INNER JOIN book_author ba ON (e.bookid = ba.bookid)
WHERE authorid = 543 AND mainname = true;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=2397.47..2398.08 rows=22 width=58) (actual time=41674.534..41675.354 rows=340 loops=1)
   ->  Sort  (cost=2397.47..2397.53 rows=22 width=58) (actual time=41674.500..41674.725 rows=340 loops=1)
         Sort Key: e.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN (e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
         Sort Method:  quicksort  Memory: 60kB
         ->  Nested Loop  (cost=0.00..2396.98 rows=22 width=58) (actual time=460.104..41673.472 rows=340 loops=1)
               ->  Index Scan using authorid1 on book_author ba  (cost=0.00..70.58 rows=19 width=8) (actual time=133.884..8063.814 rows=99 loops=1)
                     Index Cond: (authorid = 543)
               ->  Index Scan using foreign_key_bookid on editions e  (cost=0.00..122.33 rows=9 width=50) (actual time=182.089..339.474 rows=3 loops=99)
                     Index Cond: (e.bookid = ba.bookid)
                     Filter: e.mainname
 Total runtime: 41675.644 ms
(11 Zeilen)

book_author enthält ca. 2,5 mio. Tupels, editions ca. 9 mio. Sind da solche Werte nach einem DB-Neustart normal? Wenn ich die Abfrage ein zweites Mal start liegt die Zeit nur noch bei ca. 10 ms. Bei einer anderen authorid jedoch wieder sehr hoch. Immerhin kommt es mir so vor, als verringere sich die Zeit der Erstabfragen mit der Zeit. Aber ich kann ja schlecht nach jedem DB-Neustart erst Hundert Abfragen starten, damit das normal läuft?!
An der Windowfunktion scheint es jedenfalls nicht zu liegen, auch eine solche Abfrage:

explain analyse
SELECT ba.bookid
,authorid
,title
,languageid
FROM editions e
INNER JOIN book_author ba ON (e.bookid = ba.bookid)
WHERE authorid = 298 and (languageid = '123' OR languageid = '401' OR languageid = '150')
AND mainname = true;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2401.49 rows=17 width=54) (actual time=1568.099..34647.336 rows=27 loops=1)
   ->  Index Scan using authorid1 on book_author ba  (cost=0.00..70.58 rows=19 width=8) (actual time=173.333..872.933 rows=15 loops=1)
         Index Cond: (authorid = 298)
   ->  Index Scan using foreign_key_bookid on editions e  (cost=0.00..122.59 rows=7 width=50) (actual time=434.634..2251.613 rows=2 loops=15)
         Index Cond: (e.bookid = ba.bookid)
         Filter: (e.mainname AND ((e.languageid = 123) OR (e.languageid = 401) OR (e.languageid = 150)))
 Total runtime: 34647.496 ms
(7 Zeilen)

explain analyse
SELECT ba.bookid
,authorid
,title
,languageid
FROM editions e
INNER JOIN book_author ba ON (e.bookid = ba.bookid)
WHERE authorid = 298 and (languageid = '123' OR languageid = '401' OR languageid = '150')
AND mainname = true;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2401.49 rows=17 width=54) (actual time=0.138..0.914 rows=27 loops=1)
   ->  Index Scan using authorid1 on book_author ba  (cost=0.00..70.58 rows=19 width=8) (actual time=0.018..0.042 rows=15 loops=1)
         Index Cond: (authorid = 298)
   ->  Index Scan using foreign_key_bookid on editions e  (cost=0.00..122.59 rows=7 width=50) (actual time=0.021..0.054 rows=2 loops=15)
         Index Cond: (e.bookid = ba.bookid)
         Filter: (e.mainname AND ((e.languageid = 123) OR (e.languageid = 401) OR (e.languageid = 150)))
 Total runtime: 0.971 ms
(7 Zeilen)

ist erst sehr langsam, beim zweiten Abrufen dann aber sehr schnell. Wo könnte ich ansetzen die Zeit der ‘Erstabfrage’ zu verkürzen? Servereinstellungen? Query anders gestalten?

Vielen Dank für Hinweise,
Strippenzieher

Habe heute in Upgrade auf 9.1 gemacht und trotz gleicher Servereinstellungen flutscht nun alles. Vielleicht hat der Reimport des Dumps irgendwas bewirkt, oder bei 9.1 ist irgendwas anderes besser eingestellt.

Gruß, strippenzieher