Welchen Index für Punktgeometrien und Integer values?

Hallo,

ich habe eine Spalte wo ich aus dem jeweiligen Polygon Centroide berechne (2D).
Diese liegen nun in der Tabelle vor.
Ich will folgende Abfrage machen:

SELECT a.id AS building_nr, ST_AsGeoJSON(c.Geometry) AS geometry, d.Classname AS polygon_typ 

FROM building a, thematic_surface b, surface_geometry c, Objectclass d 

WHERE c.Geometry && ST_MakeEnvelope(3500967.35,5394124.36, 3501367.35,5394524.36)
AND a.id = b.building_id 
AND b.lod2_multi_surface_id = c.root_id 
AND c.geometry IS NOT NULL 
AND b.Objectclass_ID = d.ID;

Ich benutze den && Operator. Heißt das ich muss den Indextyp Gist für c.Geometry verwenden?
Und was muss ich für eine Sortierfolge und Operatorklasse verwenden?
Kann mir da jemand weiterhelfen?

Desweiteren habe ich noch eine Kachel_ID. Das ist nur ein normaler Integer Wert. Das heißt es handelt sich nicht um räumliche Daten. Sollte ich dann den btree verwenden? und wieder welche Sortierfolge + Operatorklasse?

versuiche es mal mit einem ganz normalen gist, und schau (via explain), ob der hilft. Den implizieten Join solltest Du auf expliziete Join-Syntax umschreiben - ist einfacher zu lesen. Falls c.geometry sehr oft NULL ist könnte ein partieller Index nützlich sein.

Again: explain nutzen!

Ok danke für die Info. Ich nutze den GIST-Index für meine Centroide.

Die Abfrage mit ST_WITHIN braucht allerdings ca 10,5 Sekunden!!! Genausolange wie mit a.centroid_geom && ST_MakeEnvelope()

--EXPLAIN ANALYZE
SELECT a.id AS building_nr, c.Geometry AS geometry, d.Classname AS polygon_typ 
FROM building a, thematic_surface b, surface_geometry c, Objectclass d 
--WHERE a.centroid_geom && ST_MakeEnvelope(3499367.35,5393324.36, 3501367.35,5395324.36) --2000m
WHERE ST_WITHIN(a.centroid_geom, ST_MakeEnvelope(3499367.35,5393324.36, 3501367.35,5395324.36, 31467)) --2000m
AND a.id = b.building_id 
AND b.lod2_multi_surface_id = c.root_id 
AND c.geometry IS NOT NULL 
AND b.Objectclass_ID = d.ID;

Ich möchte Laufzeiten vergleichen mit Oracle.
Da braucht diese Abfrage 2s. Da kann doch was nicht stimmen oder?
PostgreSQL ist lokal auf meinem Rechner und Oracle ist remote!
Ich verwenden in beiden Db Indizes und Output ist auch gleich. Statt ST_WITHIN() nehme ich in Oracle SDO_INSIDE().
Wieso dauert das in postgis so lange???


Zur Info:
Wenn ich EXPLAIN ANALYZE verwende kommen zusätzliche Infos
Planning time: 1.264ms
Execution time: 112.755ms

Was bedeuten diese?

was sagt Explain analyse?

EXPLAIN ANALYZE:

"Nested Loop  (cost=29.87..2166.11 rows=1193 width=202) (actual time=0.924..111.464 rows=31152 loops=1)"
"  ->  Hash Join  (cost=29.44..1247.98 rows=570 width=24) (actual time=0.915..30.410 rows=31152 loops=1)"
"        Hash Cond: (b.objectclass_id = d.id)"
"        ->  Nested Loop  (cost=26.05..1236.76 rows=570 width=12) (actual time=0.837..21.543 rows=31152 loops=1)"
"              ->  Bitmap Heap Scan on building a  (cost=25.62..718.42 rows=59 width=4) (actual time=0.824..5.140 rows=2877 loops=1)"
"                    Recheck Cond: ('0103000020EB7A00000100000005000000CDCCCCACB3B24A41713D0A17EB925441CDCCCCACB3B24A41713D0A17DF945441CDCCCCAC9BB64A41713D0A17DF945441CDCCCCAC9BB64A41713D0A17EB925441CDCCCCACB3B24A41713D0A17EB925441'::geometry ~ centroid_geo (...)"
"                    Filter: _st_contains('0103000020EB7A00000100000005000000CDCCCCACB3B24A41713D0A17EB925441CDCCCCACB3B24A41713D0A17DF945441CDCCCCAC9BB64A41713D0A17DF945441CDCCCCAC9BB64A41713D0A17EB925441CDCCCCACB3B24A41713D0A17EB925441'::geometry, centroi (...)"
"                    Rows Removed by Filter: 2"
"                    Heap Blocks: exact=55"
"                    ->  Bitmap Index Scan on centroid_geom_gix  (cost=0.00..25.61 rows=177 width=0) (actual time=0.778..0.778 rows=2879 loops=1)"
"                          Index Cond: ('0103000020EB7A00000100000005000000CDCCCCACB3B24A41713D0A17EB925441CDCCCCACB3B24A41713D0A17DF945441CDCCCCAC9BB64A41713D0A17DF945441CDCCCCAC9BB64A41713D0A17EB925441CDCCCCACB3B24A41713D0A17EB925441'::geometry ~ centroid (...)"
"              ->  Index Scan using them_surface_building_fkx on thematic_surface b  (cost=0.43..8.66 rows=13 width=12) (actual time=0.003..0.004 rows=11 loops=2877)"
"                    Index Cond: (building_id = a.id)"
"        ->  Hash  (cost=2.06..2.06 rows=106 width=20) (actual time=0.065..0.065 rows=106 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 14kB"
"              ->  Seq Scan on objectclass d  (cost=0.00..2.06 rows=106 width=20) (actual time=0.009..0.027 rows=106 loops=1)"
"  ->  Index Scan using surface_geom_root_fkx on surface_geometry c  (cost=0.43..1.43 rows=18 width=186) (actual time=0.002..0.002 rows=1 loops=31152)"
"        Index Cond: (root_id = b.lod2_multi_surface_id)"
"        Filter: (geometry IS NOT NULL)"
"        Rows Removed by Filter: 1"
"Planning time: 1.264 ms"
"Execution time: 112.755 ms"

Das bedeutet, dass die Ausführung in Postgres mehr als 15 mal so schnell ist wie in Oracle:

“Execution time: 112.755ms” bedeutet, das Statement hat nur 112 Millisekunden benötigt um auf den Server ausgeführt zu werden (also 0,1 Sekunde).

Wenn also Dein SQL Client länger als 1 Sekunde benötigt, dann braucht der halt so lang um 30.000 Datensätze darzustellen.

Wenn Du die Performance für die Anzeige von Datensätzen vergleichen willst, dann nimm den gleichen SQL Client für Oracle und Postgres.

Das heißt wenn ich die Performance zur Ausführung der Abfrage vergleichen will nehme ich die “Execution time” in PostgreSQL.
Weißt jemand zufällig wie ich die Execution time in Oracle feststelle (SET AUTOTRACE ON EXPLAIN):


| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 5567 | 1185K| | 67617 (1)| 00:13:32 |
|* 1 | HASH JOIN | | 5567 | 1185K| | 67617 (1)| 00:13:32 |
| 2 | TABLE ACCESS FULL | OBJECTCLASS | 106 | 2226 | | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 5567 | 1070K| 4136K 67614 (1)| 00:13:32 |
|* 4 | HASH JOIN | | 33309 | 3740K| | 1809 (2)| 00:00:22 |
| 5 | TABLE ACCESS BY INDEX ROWID | BUILDING | 3247 | 317K| | 37 (0)| 00:00:01 |
|* 6 | DOMAIN INDEX (SEL: 1.831968 %) | CENTROID_GEOM_SPX | | | | 37 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | THEMATIC_SURFACE | 1712K| 24M| | 1763 (2)| 00:00:22 |
|* 8 | TABLE ACCESS FULL | SURFACE_GEOMETRY | 284K| 22M| | 64337 (1)| 00:12:53 |

letzte Spalte? Time?

Bei Oracle kann man das am Execution plan nicht sehen.

Am ehesten geht das noch mit “SET AUTOTRACE TRACEONLY”. Damit werden die Daten nicht am Client angezeigt - allerdings werden sie trotzdem übertragen, d.h. der Netzwerk Overhead geht weiterhin in die Gesamtzeit ein. Kann man z.B. sehen wenn man ein SET ARRAY 100 macht, das verringert die Anzahl der Roundtrips und beschleunigt das dann. Details dazu kannst Du Dir via “SET AUTOTRACE TRACEONLY STATISTICS” anzeigen lassen.