[postgis-users] query optimization

Markus Schaber schabios at logi-track.com
Wed Dec 15 05:34:37 PST 2004


On Tue, 14 Dec 2004 16:13:40 -0800
Paul Ramsey <pramsey at refractions.net> wrote:

> If you upgrade to the CVS head (soon to be 1.0RC1), you can use the 
> lightweight geometries. Since your data is points, you will get a *huge* 
> boost in terms of storage efficiency. (about 4:1 for points I think). A 
> smaller on-disk representation means less disk hits to pull the same 
> amount of data through the system.

Depending on the size of your geometries and the other columns of your
table, using lwgeoms can drastically reduce the amount of data that gets
put out into the toast tables. Each "toasted" row needs at least two
more pages (toast index, toast data) to be fetched from disk when read.

[FYI: If you don't know, what toast is: Older PostgreSQL had a hard
limit of the size of a row, it could not be larger than a single page
minus page meta data. Current PostgreSQL versions use compression of
large values (e. G. texts), and can put the data that still does not fit
into a so-called TOAST table. This way, a single field can grow up to 1
Gig, AFAIR.]


markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios at logi-track.com | www.logi-track.com

More information about the postgis-users mailing list