Seltsame Ergebnisse mit ORDER BY

Seltsame Ergebnisse mit ORDER BY

am 04.10.2011 16:40:04 von Martin Spott

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 natuerlich die Ergebnismenge nochmal in ein Perl-Array stecken
und dort erneut sortieren, aber das kann keine Loesung sein. Verratet
Ihr mir, wo Ihr ansetzen wuerdet, um der eigentlichen Ursache auf den
Grund zu gehen ?

Schoenen Tach,
Martin.
--
Unix _IS_ user friendly - it's just selective about who its friends are !
------------------------------------------------------------ --------------

--
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Re: Seltsame Ergebnisse mit ORDER BY

am 04.10.2011 17:05:52 von Andreas Kretschmer

Martin Spott wrote:

> Tach zusammen,
>=20
> 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:
>=20
> http://scenemodels.flightgear.org/models.php
>=20
> 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.
>=20
> Die Tabelle sieht etwa so aus:
>=20
> Column | Type | =
Modifiers =20
> ---------------+-----------------------------+-------------- -----------=
--------------------------------------------
> ob_id | integer | not null default nextval=
('fgs_objects_ob_id_seq'::regclass)
> ob_modified | timestamp without time zone |=20
> ob_deleted | timestamp without time zone | not null default '1970-0=
1-01 00:00:01'::timestamp without time zone
> ob_text | character varying(100) |=20
> wkb_geometry | geometry |=20
> ob_gndelev | numeric(7,2) | default (-9999.00)
> ob_elevoffset | numeric(5,2) |=20
> ob_peakelev | numeric(7,2) |=20
> ob_heading | numeric(5,2) | default 0.00
> ob_country | character(2) |=20
> ob_model | integer |=20
> ob_group | integer |=20
> ob_tile | integer |=20
> ob_reference | character varying(20) |=20
> ob_submitter | character varying(16) | default 'unknown'::chara=
cter varying
> ob_valid | boolean | default true
> ob_class | character varying(10) |=20
>=20
>=20
> .... 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:
>=20
> landcover=3D> SELECT ob_tile, ob_model, y(ST_AsText(wkb_geometry)) AS o=
b_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_gnd=
elev, ob_heading;
> ob_tile | ob_model | ob_lat | ob_lon | ob_gndelev | ob_headin=
g=20
> ---------+----------+------------+-------------+------------ +----------=
--
> 4688 | 25 | -16.688333 | -179.880556 | -0.02 | 0.0=
0
> 9050 | 25 | 51.379658 | -179.258372 | -0.29 | 0.0=
0
> 9050 | 39 | 51.379658 | -179.258372 | -0.29 | 0.0=
0
> 9953 | 25 | 65.516667 | -179.283333 | 0.17 | 0.0=
0
> 10160 | 37 | 68.87 | -179.558056 | 198.12 | 0.0=
0
> 10169 | 25 | 68.905 | -179.456667 | 22.18 | 0.0=
0
> 20818 | 25 | -20.653333 | -178.741667 | -0.02 | 0.0=
0
> 20977 | 25 | -18.205 | -178.813333 | 77.91 | 0.0=
0
> 21231 | 25 | -14.32 | -178.053333 | -0.03 | 0.0=
0
> 40330 | 25 | 28.203672 | -177.379547 | 0.00 | 0.0=
0
> [...]
>=20
>=20
> Das ist ja auch alles prima .... abgesehen davon, dass wir, wie im
> Beispiel, manche Duplikate da drin haben ....
>=20
> 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.
>=20
> 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.=20
> 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.
>=20
> 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 mi=
t
drin sind, u.U. Hashwerte der zu joinenden Spalten.

All das dürfte aber nicht zu echt falschen Sortierungen führen, sonde=
rn
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
--=20
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=
°

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein