PostgreSQL - Tipps und Tricks: Unterschied zwischen den Versionen
Aus Geoinformation HSR
Stefan (Diskussion | Beiträge) (→PostgreSQL optimieren) |
Stefan (Diskussion | Beiträge) (→Quickreference) |
||
Zeile 9: | Zeile 9: | ||
* Start pgsl-Client: | * Start pgsl-Client: | ||
− | + | > psql -h localhost -p 5432 template1 "postgres" | |
+ | oder | ||
+ | > cmd /c chcp 1252 | ||
+ | > psql -d gisdb -U postgres | ||
+ | > cmd /c chcp 850 | ||
== PostgreSQL SQL == | == PostgreSQL SQL == |
Version vom 14. Januar 2010, 02:20 Uhr
Siehe auch:
Inhaltsverzeichnis
Quickreference
Im Aufbau...
- Start pgsl-Client:
> psql -h localhost -p 5432 template1 "postgres" oder > cmd /c chcp 1252 > psql -d gisdb -U postgres > cmd /c chcp 850
PostgreSQL SQL
Version:
select version();
Anfrage des Geometrie-Typs ('the_geom' ist der Name des Geometrie-Felds):
SELECT * FROM sometable WHERE GeometryType(the_geom) = 'GEOMETRYCOLLECTION'
Problem mit Datenfelder (for < 8.3):
SELECT the_geom ... (end_date - NOW() >= 10) AS GREEN AND (end_date - NOW() < 0) AS RED ... FROM ...
Casting changes in >= 8.3:
SELECT the_geom ... (end_date - NOW() >= '10 DAYS'::INTERVAL) AS GREEN AND (end_date - NOW() < '0'::INTERVAL) AS RED ... FROM ...
SELECT the_geom ... EXTRACT(DAY FROM (end_date - NOW()) >= 10) AS GREEN AND EXTRACT(DAY FROM (end_date - NOW()) < 0) AS RED ... FROM ...
Konfiguration
Die pg_hba.conf kann so eingestellt werden, dass bei der Ausführung von PostgreSQL-Kommandozeilen-Tools kein Passwort übertragen werden muss.
Bsp mit 127.0.0.1: Anstelle
# IPv4 local connections: host all all 127.0.0.1/32 md5
neu:
# IPv4 local connections: host all all 127.0.0.1/32 trust
PostgreSQL optimieren
- Make sure you have (spatial) indexes on your tables.
- Cluster on (spatial) indexes tends to improve performance but mostly good for static spatial data.
- PostGIS: A spatial index is more effective on small geometries than larger geometries even for MySQL.
- EXPLAIN ANALYZE is your friend -- especially PgAdmin III Graphical Explain - will pinpoint when indexes are not being used and what navigation strategies are being used.
- posgresql.conf - fiddling with that a lot has been written -- particularly work_mem is important.
- Then there is a whole science on how you write sql queries. In general for PostgreSQL - you can force PostgreSQL to materialize subselects by putting in an ORDER BY and in general a subselect tends to force the planner to evaluate those as a single unit of work, which tends to improve speed in many cases.
Siehe auch: