pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [postgis-users] Is my query planner failing me, or vice versa?
5+ messages / 4 participants
[nested] [flat]
* Re: [postgis-users] Is my query planner failing me, or vice versa?
@ 2005-12-15 00:23 Gregory S. Williamson <gsw@globexplorer.com>
2005-12-15 05:36 ` Re: [postgis-users] Is my query planner failing me, or vice versa? Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 11:03 ` Re: [postgis-users] Is my query planner failing me, or vice versa? Markus Schaber <schabi@logix-tt.com>
0 siblings, 2 replies; 5+ messages in thread
From: Gregory S. Williamson @ 2005-12-15 00:23 UTC (permalink / raw)
To: pgsql-performance; +Cc: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
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.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [postgis-users] Is my query planner failing me, or vice versa?
2005-12-15 00:23 Re: [postgis-users] Is my query planner failing me, or vice versa? Gregory S. Williamson <gsw@globexplorer.com>
@ 2005-12-15 05:36 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-12-15 05:36 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: pgsql-performance@postgresql.org, "PostGIS Users Discussion" <postgis-users@postgis.refractions.net>
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [postgis-users] Is my query planner failing me, or vice versa?
2005-12-15 00:23 Re: [postgis-users] Is my query planner failing me, or vice versa? Gregory S. Williamson <gsw@globexplorer.com>
@ 2005-12-15 11:03 ` Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 5+ messages in thread
From: Markus Schaber @ 2005-12-15 11:03 UTC (permalink / raw)
To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [postgis-users] Is my query planner failing me, or vice versa?
@ 2005-12-15 12:03 Gregory S. Williamson <gsw@globexplorer.com>
2005-12-15 14:20 ` Re: [postgis-users] Is my query planner failing me,or vice versa? Mark Cave-Ayland <m.cave-ayland@webbased.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Gregory S. Williamson @ 2005-12-15 12:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance; PostGIS Users Discussion <postgis-users@postgis.refractions.net>
Well, what does the random_page_cost do internally ?
I don't think I'd expect postgres to be able to *do* anything in particular, any more than I would expect it to "do" something about slow disk I/O or having limited cache. But it might be useful to the EXPLAIN ANALYZE in estimating costs of retrieving such data.
Admittedly, this is not as clear as wanting a sequential scan in preference to indexed reads when there are either very few rows or a huge number, but it strikes me as useful to me the DBA to have this factoid thrust in front of me when considering why a given query is slower than I might like. Perhaps an added time based on this factor and the random_page_cost value, since lots of TOAST data and a high access time would indicate to my (ignorant!) mind that retrieval would be slower, especially over large data sets.
Forgive my ignorance ... obviously I am but a humble user. grin.
G
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wed 12/14/2005 9:36 PM
To: Gregory S. Williamson
Cc: pgsql-performance@postgresql.org; PostGIS Users Discussion
Subject: Re: [PERFORM] [postgis-users] Is my query planner failing me, or vice versa?
"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
!DSPAM:43a100d6285261205920220!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [postgis-users] Is my query planner failing me,or vice versa?
2005-12-15 12:03 Re: [postgis-users] Is my query planner failing me, or vice versa? Gregory S. Williamson <gsw@globexplorer.com>
@ 2005-12-15 14:20 ` Mark Cave-Ayland <m.cave-ayland@webbased.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Mark Cave-Ayland @ 2005-12-15 14:20 UTC (permalink / raw)
To: 'PostGIS Users Discussion' <postgis-users@postgis.refractions.net>; 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wed 12/14/2005 9:36 PM
> To: Gregory S. Williamson
> Cc: pgsql-performance@postgresql.org; PostGIS Users Discussion
> Subject: Re: [PERFORM] [postgis-users] Is my query planner failing
me,
> or vice versa?
> "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
>
>
> -----Original Message-----
> From: postgis-users-bounces@postgis.refractions.net [mailto:postgis-users-
> bounces@postgis.refractions.net] On Behalf Of Gregory S. Williamson
> Sent: 15 December 2005 12:03
> To: Tom Lane
> Cc: pgsql-performance@postgresql.org; PostGIS Users Discussion
> Subject: RE: [PERFORM] [postgis-users] Is my query planner failing me,or
> vice versa?
>
> Well, what does the random_page_cost do internally ?
>
> I don't think I'd expect postgres to be able to *do* anything in
> particular, any more than I would expect it to "do" something about slow
> disk I/O or having limited cache. But it might be useful to the EXPLAIN
> ANALYZE in estimating costs of retrieving such data.
>
> Admittedly, this is not as clear as wanting a sequential scan in
> preference to indexed reads when there are either very few rows or a huge
> number, but it strikes me as useful to me the DBA to have this factoid
> thrust in front of me when considering why a given query is slower than I
> might like. Perhaps an added time based on this factor and the
> random_page_cost value, since lots of TOAST data and a high access time
> would indicate to my (ignorant!) mind that retrieval would be slower,
> especially over large data sets.
>
> Forgive my ignorance ... obviously I am but a humble user. grin.
>
> G
As I understood from the original discussions with Markus/Tom, the problem
was that the optimizer didn't consider the value of the VacAttrStats
stawidth value when calculating the cost of a sequential scan. I don't know
if this is still the case though - Tom will probably have a rough idea
already whereas I would need to spend some time sifting through the source.
However, I do know that the PostGIS statistics collector does store the
average detoasted geometry size in stawidth during ANALYZE so the value is
there if it can be used.
Kind regards,
Mark.
------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT
T: +44 (0)1752 797131
F: +44 (0)1752 791023
http://www.webbased.co.uk
http://www.infomapper.com
http://www.swtc.co.uk
This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-12-15 14:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-15 00:23 Re: [postgis-users] Is my query planner failing me, or vice versa? Gregory S. Williamson <gsw@globexplorer.com>
2005-12-15 05:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 11:03 ` Markus Schaber <schabi@logix-tt.com>
2005-12-15 12:03 Re: [postgis-users] Is my query planner failing me, or vice versa? Gregory S. Williamson <gsw@globexplorer.com>
2005-12-15 14:20 ` Re: [postgis-users] Is my query planner failing me,or vice versa? Mark Cave-Ayland <m.cave-ayland@webbased.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox