Re: Seltsame Ergebnisse mit ORDER BY

Martin Spott <Martin(dot)Spott(at)mgras(dot)net> wrote:

> Tach zusammen,
>
> seit laengerer Zeit beobachten wir ein Phaenomen, fuer das wir bisher
> nicht den Hauch einer Erklaerung haben, weil es einfach unserem
> grundsaetzlichen Verstaendnis von der Funktion der eingesetzten
> Werkzeuge widerspricht. In unserer huebschen PostGIS- Datenbank, die
> das Backend fuer diese Seite hier spielt:
>
> http://scenemodels.flightgear.org/models.php
>
> speichern wir 3D-Modelle fuer die FlightGear-Szenerie. Die eine Tabelle
> haelt die Modelle von Haeusern, Schornsteinen und anderem Gedoens, eine
> andere Tabelle haelt die Positionen fuer die Modelle und speichert
> unter Anderem jeweils a) eine Kachelnummer (die Szenerie ist in gaaanz
> vielen kleinen Kacheln organisiert), b) 'ne numerische ID mit der
> Referenz zu dem Modell, was an die betreffende Position gestellt werden
> soll, c) eine geographische Position, d) eine Gelaende-Hoehe ueber NN
> und e) eine Richtung.
>
> Die Tabelle sieht etwa so aus:
>
> Column | Type | Modifiers
> ---------------+-----------------------------+---------------------------------------------------------------------
> ob_id | integer | not null default nextval('fgs_objects_ob_id_seq'::regclass)
> ob_modified | timestamp without time zone |
> ob_deleted | timestamp without time zone | not null default '1970-01-01 00:00:01'::timestamp without time zone
> ob_text | character varying(100) |
> wkb_geometry | geometry |
> ob_gndelev | numeric(7,2) | default (-9999.00)
> ob_elevoffset | numeric(5,2) |
> ob_peakelev | numeric(7,2) |
> ob_heading | numeric(5,2) | default 0.00
> ob_country | character(2) |
> ob_model | integer |
> ob_group | integer |
> ob_tile | integer |
> ob_reference | character varying(20) |
> ob_submitter | character varying(16) | default 'unknown'::character varying
> ob_valid | boolean | default true
> ob_class | character varying(10) |
>
>
> .... und dann kommen noch ein paar Indices.
> Wenn wir die Positionen aus der Datenbank exportieren, dann sortieren
> wir nach genau den beschriebenen Kriterien und zwar mit einem simplen
> Ausdruck. Wenn man sich so ein Ergebnis mal exemplarisch anguckt, kann
> das etwa so aussehen:
>
> landcover=> SELECT ob_tile, ob_model, y(ST_AsText(wkb_geometry)) AS ob_lat, x(ST_AsText(wkb_geometry)) AS ob_lon, ob_gndelev, ob_heading
> landcover-> FROM fgs_objects
> landcover-> ORDER BY ob_tile, ob_model, ST_AsText(wkb_geometry), ob_gndelev, ob_heading;
> ob_tile | ob_model | ob_lat | ob_lon | ob_gndelev | ob_heading
> ---------+----------+------------+-------------+------------+------------
> 4688 | 25 | -16.688333 | -179.880556 | -0.02 | 0.00
> 9050 | 25 | 51.379658 | -179.258372 | -0.29 | 0.00
> 9050 | 39 | 51.379658 | -179.258372 | -0.29 | 0.00
> 9953 | 25 | 65.516667 | -179.283333 | 0.17 | 0.00
> 10160 | 37 | 68.87 | -179.558056 | 198.12 | 0.00
> 10169 | 25 | 68.905 | -179.456667 | 22.18 | 0.00
> 20818 | 25 | -20.653333 | -178.741667 | -0.02 | 0.00
> 20977 | 25 | -18.205 | -178.813333 | 77.91 | 0.00
> 21231 | 25 | -14.32 | -178.053333 | -0.03 | 0.00
> 40330 | 25 | 28.203672 | -177.379547 | 0.00 | 0.00
> [...]
>
>
> Das ist ja auch alles prima .... abgesehen davon, dass wir, wie im
> Beispiel, manche Duplikate da drin haben ....
>
> Jetzt kommt es aber vor, dass wir Eintraege haben, die einander _fast_
> gleich sind und die vielleicht nur in der Gelaende-Hoehe variieren. Da
> steht dann halt ein Eintrag mal mit -30.2 Metern Hoehe in der Tabelle
> und noch ein zweites Mal mit -65.2.
>
> Inhaltlich ist das immer noch Bloedsinn, aber es geht mir um 'was
> anderes: Solche Eintraege werden aus unerfindlichen Gruenden manchmal
> am einen Tag anders sortiert als am vorangegangenen. Wenn ich z.B.
> spasseshalber ein CLUSTER oder REINDEX ueber die Tabelle (oder die
> ganze Datenbank) laufen lasse, kann ich sicher sein, dass am naechsten
> Tag etliche Eintraege anders sortiert werden als am Vortag.
>
> Das will mir einfach nicht in den Kopf, weil die Nutzdaten vollkommen
> unveraendert geblieben sind. Klar, mit CLUSTER oder REINDEX veraendere
> ich natuerlich den Index oder die Sortier-Reihenfolge in der rohen
> Tabelle, aber das sollte einem "ORDER BY" doch vollkommen egal sein,
> zumal dann, wenn, etwa im Fall -30.2 zu -65.2 die Sortier-Kriterien
> doch wirklich eindeutig sind.

Ich kann Deine Zahlen hier mit Deinen Daten oben irgendwie nicht in
Einklang bringen.

Ist das dann echt *falsch* sortiert, oder nur anders?

Einfluß auf Sorierung/Ausgabe hat nicht nur das ORDER BY, sondern auch,
und zwar wenn die Spalte nicht im ORDER BY ist, die physische Anordnung
der Datensätze, aber auch z.B. ob es parallele Scans gibt (seit IIRC 8.3
werden Seq-Scans, wenn es geht, parallel ausgeführt) und, wenn JOINS mit
drin sind, u.U. Hashwerte der zu joinenden Spalten.

All das dürfte aber nicht zu echt falschen Sortierungen führen, sondern
nur dazu, daß ohne expliete Sortierung Du Dich eben NICHT auf eine
bestimmte Sortierung verlassen kannst und daß, selbst bei 'statischen'
Tabellen die Ausgabe bei jeder Abfrage anders aussehen kann.

Ich weiß nun nicht, ob Dir meine Aussage schon hilft ...

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°