Welchen Index für Punktgeometrien und Integer values?

Probleme, Fragen die mit der Installation und Anwendung der PostGIS-Erweiterung zu tun haben.
Antworten
limo1013
Neuer Benutzer
Beiträge: 12
Registriert: Di 12. Apr 2016, 17:41

Welchen Index für Punktgeometrien und Integer values?

Beitrag von limo1013 » Di 5. Jul 2016, 17:05

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:

Code: Alles auswählen

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?

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Welchen Index für Punktgeometrien und Integer values?

Beitrag von akretschmer » Di 5. Jul 2016, 17:40

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!
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

limo1013
Neuer Benutzer
Beiträge: 12
Registriert: Di 12. Apr 2016, 17:41

Re: Welchen Index für Punktgeometrien und Integer values?

Beitrag von limo1013 » Mi 6. Jul 2016, 12:28

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()

Code: Alles auswählen

--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?
Zuletzt geändert von limo1013 am Mi 6. Jul 2016, 12:36, insgesamt 2-mal geändert.

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Welchen Index für Punktgeometrien und Integer values?

Beitrag von akretschmer » Mi 6. Jul 2016, 12:35

was sagt Explain analyse?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

limo1013
Neuer Benutzer
Beiträge: 12
Registriert: Di 12. Apr 2016, 17:41

Re: Welchen Index für Punktgeometrien und Integer values?

Beitrag von limo1013 » Mi 6. Jul 2016, 12:37

EXPLAIN ANALYZE:

Code: Alles auswählen

"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"

castorp
Erfahrener Benutzer
Beiträge: 1033
Registriert: Sa 27. Jun 2009, 22:24

Re: Welchen Index für Punktgeometrien und Integer values?

Beitrag von castorp » Mi 6. Jul 2016, 12:48

limo1013 hat geschrieben: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?
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.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

limo1013
Neuer Benutzer
Beiträge: 12
Registriert: Di 12. Apr 2016, 17:41

Re: Welchen Index für Punktgeometrien und Integer values?

Beitrag von limo1013 » Mi 6. Jul 2016, 13:11

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 |
----------------------------------------------------------------------------------------------------------------

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Welchen Index für Punktgeometrien und Integer values?

Beitrag von akretschmer » Mi 6. Jul 2016, 14:48

letzte Spalte? Time?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

castorp
Erfahrener Benutzer
Beiträge: 1033
Registriert: Sa 27. Jun 2009, 22:24

Re: Welchen Index für Punktgeometrien und Integer values?

Beitrag von castorp » Mi 6. Jul 2016, 14:48

limo1013 hat geschrieben: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)
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.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Antworten