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

Forgive the cross-posting, but I found myself wondering if might not be some way future way of telling the planner that
agiven table (column ?) has a high likelyhood of being TOASTed. Similar to the random_page_cost in spirit. We've got a
lotof indexed data that is spatial and have some table where no data is toasted (road segments) and others where
evrythingis. 

An idle suggestion from one who knows that he is meddling with ;-}

Greg Williamson
DBA
GlobeXplorer LLC

> -----Original Message-----
> From: postgis-users-bounces@postgis.refractions.net
> [mailto:postgis-users-bounces@postgis.refractions.net]On Behalf Of
> Jessica M Salmon
> Sent: Wednesday, December 14, 2005 9:09 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Is my query planner failing me, or vice versa?
>
> Thanks, Marcus, for explaining.
>
> And thanks, Robert, for asking that question about adjusting page size.
>
> My tuples are definitely toasted (some of my geometries are 30X too big for
> a single page!), so I'm glad I'm aware of the TOAST tables now. I suppose
> there's not much to be done about it, but it's good to know.
>
> Thanks everyone for such an array of insightful help.
>
> -Meghan

"Gregory S. Williamson" <gsw@globexplorer.com> writes:
> Forgive the cross-posting, but I found myself wondering if might not
> be some way future way of telling the planner that a given table
> (column ?) has a high likelyhood of being TOASTed.

What would you expect the planner to do with the information, exactly?

We could certainly cause ANALYZE to record some estimate of this, but
I'm not too clear on what happens after that...

            regards, tom lane

Hi, Gregory,

Gregory S. Williamson wrote:
> Forgive the cross-posting, but I found myself wondering if might not
> be some way future way of telling the planner that a given table
> (column ?) has a high likelyhood of being TOASTed. Similar to the
> random_page_cost in spirit. We've got a lot of indexed data that is
> spatial and have some table where no data is toasted (road segments)
> and others where evrything is.

I'd personally put this into ANALYZE, it already collects statistics, so
it could also calculate TOASTing likelyhood and average TOASTed size.

Maybe that 8.X PostgreSQL already does this, I'm a bit lagging :-)

Markus