PostgreSQL - Tipps und Tricks

Siehe auch:

• PostgreSQL
• PostGIS und PostGIS - Tipps und Tricks

Quickreference

Im Aufbau...

Tipps und Tricks...

• psql und wohl alle weiteren PostgreSQL-Tools (dropdb, createdb, pg_dump, etc.) u.a. brauchen Angaben zu Benutzer und Passwort. Der Benutzer wird mit Parameter -U mitgegeben, das Passwort hingegen kann (mit Absicht) nicht mitgegeben werden. Es gibt aber die Möglichkeit, Umgebungsvariablen zu setzen:

 SET PGUSER=postgres
 SET PGPASSWORD=<meinpasswort>

• Start pgsl-Client:

 > psql -h localhost -p 5432 template1 "postgres"
 oder
 > cmd /c chcp 1252
 > psql -d gisdb -U postgres
 postgres=# SET CLIENT_ENCODING TO 'LATIN1';
 postgres=# <do whatever you must do in psql...>
 > cmd /c chcp 850

PostgreSQL SQL

Siehe auch im Wiki der Vorlesung Datenbanken (Dbs1) von Prof. S. Keller.

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

Stefan's Tipps:

1. Make sure you have (spatial) indexes on your tables.
2. Cluster on (spatial) indexes tends to improve performance but mostly good for static spatial data.
3. PostGIS: A spatial index is more effective on small geometries than larger geometries even for MySQL.
4. 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.
5. posgresql.conf - fiddling with that a lot has been written -- particularly work_mem is important.
6. 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.

SQL:

• Postgres kennt "Partial index": Beispiel: "create index i on table2(field2) where field2 <> 0;" Optimiert z.B. folgende Query: select * from table2 where field2<>0;

Konfiguration mit postgresql.conf (siehe z.B. [1]):

• Auto Vacuum einschalten
• Sort memory parameter - speziell bei order-by oder group-by Klauseln
• work_mem parameter
• Shared buffers parameter
• random_page_cost parameter – speziell bei schnellen Disks wie SCSI und RAID

Tuning your PostgreSQL Database Server:

• http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server