PostgreSQL Tips and Tricks
Couple of weeks ago, I wrote an article about writing PostGIS Spatial Queries (here).
Today I found the following post: PostgreSQL Tips & Tricks. There are a couple of tips for working with a PostgreSQL database. Here’s a summary:
- don’t do sequential scans, use indexes instead;
- index all foreign keys;
- denormalize can sometimes help;
- avoid joins;
- don’t do unachored text searches or full text searching;
- use the COPY command for bulk data loading;
- use multi-column indexes (I didn’t know this one…);
- don’t bother indexing evenly distributed booleans or enum columns;
- use EXPLAIN ANALYZE to benchmark and compare queries;
- use expression indexes (didn’t know this either!);
- indexes can have WHERE clauses;
- don’t use the database if it isn’t a good fit!
Nice article! A must read for all the postgresql & postgis users out there!
