shp2pgsql -s 4326 -D -I ne_10m_admin_0_countries.shp countries | psql performance shp2pgsql -s 4326 -D -I ne_10m_populated_places.shp places | psql performance
SELECT count(*) FROM countries WHERE ST_NPoints(geom) > (8192 / 16);
SELECT count(*), c.name FROM countries c JOIN places p ON ST_Intersects(c.geom, p.geom) GROUP BY c.name;
-- Change the storage type ALTER TABLE countries ALTER COLUMN geom SET STORAGE EXTERNAL; -- Force the column to rewrite UPDATE countries SET geom = ST_SetSRID(geom, 4326); vacuum full countries; -- Re-run the query SELECT count(*), c.name FROM countries c JOIN places p ON ST_Intersects(c.geom, p.geom) GROUP BY c.name;
PLAIN prevents either compression or out-of-line storage; furthermore it disables use of single-byte headers for varlena types. This is the only possible strategy for columns of non-TOAST-able data types.
EXTENDED allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big. EXTERNAL allows out-of-line storage but not compression. Use of EXTERNAL will make substring operations on wide text and bytea columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed. MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23503672/viewspace-2215524/，如需转载，请注明出处，否则将追究法律责任。