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

[Markus Schaber]

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