[postgis-users] Is my query planner failing me, or vice versa?

[Robert Burgholzer]

Markus,
Does this suggest some type of opportunity to optimize PostgreSQL for
GIS databases by changing the page size to something larger, say, enough
to accommodate the 60-80% of your largest geometry column and data row? 
This may be a dumb question, and it may not be as easy as changing the
constant and recompiling... I was just wondering.

Robert

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Markus Schaber
Sent: Wednesday, December 14, 2005 5:16 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Is my query planner failing me, or vice
versa?

Hi, Jessica,

Jessica M Salmon wrote:

> I'm afraid I've never heard of a toasted tuple. I'll have to do a
little
> research on this one before I have anything intelligent (i.e. no
limericks
> or puns) to say in response to your suggestion.

The basic thing is that PostgreSQL uses a page size of 8k (by default,
compile-time constant).

Originally, a row had to fit into a single page, plus some page meta
information (thus restricting the maximum size of a row to about 8k).

The PostgreSQL developers then invented the so-called TOAST tables. If
your row doesn't fit into 8k, then the columns with the largest data
volume get compressed and/or moved out into those TOAST tables, and the
row itsself contains pointers to the bunch of TOAST pages that store
your data.

So large (in terms of vertex count) geometry objects are not part of the
main table, but stored in those TOAST tables. But the query estimator
does not see this externalized amount of data when calculating his row
size estimations, he only sees the size of the TOAST pointers, and thus
sometimes creates suboptimal query plans.

Some more info about the TOAST tables is on
http://www.pgsql.info/pg/toast.php while the misestimation issue is
discussed e. G. on
http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php

HTH,
Markus
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users