PostgreSQL - Tipps und Tricks: Unterschied zwischen den Versionen
Stefan (Diskussion | Beiträge) |
Stefan (Diskussion | Beiträge) (→Meine unsortierten Notizen...) |
||
Zeile 28: | Zeile 28: | ||
<code> | <code> | ||
− | + | COPY geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) FROM stdin; | |
− | + | catalog public planet_osm_point way 2 900913 POINT | |
− | + | catalog public planet_osm_line way 2 900913 LINESTRING | |
− | + | public planet_osm_polygon way 2 900913 GEOMETRY | |
− | + | \. | |
</code> | </code> | ||
Version vom 5. Januar 2011, 22:23 Uhr
Siehe auch:
Inhaltsverzeichnis
Meine unsortierten Notizen...
Tools und Konfiguration
- 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
"Read-only User"
Daten laden/importieren
COPY Syntax:
- Tab-Delimited
COPY geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) FROM stdin;
catalog public planet_osm_point way 2 900913 POINT
catalog public planet_osm_line way 2 900913 LINESTRING
public planet_osm_polygon way 2 900913 GEOMETRY
\.
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 ...
hstore
Stichworte: Key-Value-Attributes, Hashtable, nosql-Database.
tbd.
Data Warehousing mit PostgreSQL
Stichworte: PostgreSQL-Module cube, PostgreSQL-Module tablefunc (mit crosstab).
tbd.
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-Optimierung
Bevor man beginnt und postgresql.conf etc. verändert...
Tipps zur Optimierung (Optimization), Performance, Tuning von PostgreSQL...:
- 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.
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; (vgl. Doc.).
Konfiguration mit postgresql.conf (siehe z.B. [2]):
- Auto Vacuum einschalten
- Parameter heraufsetzen:
- Sort memory - speziell bei order-by oder group-by Klauseln
- random_page_cost – speziell bei schnellen Disks wie SCSI und RAID
- Effective Cache Size, work_mem und Shared buffers - speziell bei genügend Memory und wenigen Applikationen und Benutzern
Siehe auch:
- Tuning your PostgreSQL Server
- RevSys (Hardware, Tuning, Explain, etc.), PostgreSQL 8 Performance Checklist (genaue Erklärung der Variablen in postgresql.conf, RAID Hinweise. Für kleinere bis mittlere PostgreSQL Installationen mit primär Leseoperationen empfiehlt sich RAID 0 oder RAID 1. RAID 0 hat jedoch keine Redundanz. RAID 5 hat schlechte Performanz, ausser wenn 6 oder mehr Disks. SCSI Ultra 320 Disks sind zwar teurer aber immer noch wesentlich schneller als S-ATA/IDE Disks.
- Das Tool pgtune macht Verbesserungsvorschläge der Konfiguration.
Weitere Tipps...
- Code snippets auf Postgres Wiki