pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSpotting planner errors (was Re: Query planner is using wrong index.)
3+ messages / 2 participants
[nested] [flat]
* Spotting planner errors (was Re: Query planner is using wrong index.)
@ 2006-04-07 15:08 Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2006-04-07 15:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Brian Herlihy <btherl@yahoo.com.au>; pgsql-performance
Tom Lane wrote:
> Brian Herlihy <btherl@yahoo.com.au> writes:
>> Before I go, I have a question - From discussions on the Postgresql irc
>> channel, and from reading the TODO list on the website, I am under the
>> impression that there are no plans to allow optimizer hints, such as "use index
>> table_pkey". Is this really true?
>
> I personally don't think it's a good idea: the time spent in designing,
> implementing, and maintaining a usable hint system would be significant,
> and IMHO the effort is better spent on *fixing* the optimizer problems
> than working around them.
Tom - does the planner/executor know it's got row estimates wrong? That
is, if I'm not running an EXPLAIN ANALYSE is there a point at which we
could log "planner estimate for X out by factor of Y"?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Spotting planner errors (was Re: Query planner is using wrong index.)
@ 2006-04-07 15:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2006-04-07 15:12 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Brian Herlihy <btherl@yahoo.com.au>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> Tom - does the planner/executor know it's got row estimates wrong? That
> is, if I'm not running an EXPLAIN ANALYSE is there a point at which we
> could log "planner estimate for X out by factor of Y"?
Not at the moment, but you could certainly imagine changing the executor
to count rows even without EXPLAIN ANALYZE, and then complain during
plan shutdown.
Not sure how helpful that would be; there would be a lot of noise from
common cases such as executing underneath a LIMIT node.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Spotting planner errors (was Re: Query planner is using
@ 2006-04-07 15:24 Richard Huxton <dev@archonet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2006-04-07 15:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Brian Herlihy <btherl@yahoo.com.au>; pgsql-performance
Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> Tom - does the planner/executor know it's got row estimates wrong? That
>> is, if I'm not running an EXPLAIN ANALYSE is there a point at which we
>> could log "planner estimate for X out by factor of Y"?
>
> Not at the moment, but you could certainly imagine changing the executor
> to count rows even without EXPLAIN ANALYZE, and then complain during
> plan shutdown.
>
> Not sure how helpful that would be; there would be a lot of noise from
> common cases such as executing underneath a LIMIT node.
Hmm - thinking about it you'd probably want to record it similarly to
stats too. It's the fact that the planner *repeatedly* gets an estimate
wrong that's of interest.
Would it be prohibitive to total actions taken - to act as raw data for
random_page_cost / cpu_xxx_cost? If you could get a ratio of estimated
vs actual time vs the various page-fetches/index-fetches etc. we could
actually plug some meaningful numbers in.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-07 15:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-07 15:08 Spotting planner errors (was Re: Query planner is using wrong index.) Richard Huxton <dev@archonet.com>
2006-04-07 15:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-07 15:24 ` Re: Spotting planner errors (was Re: Query planner is using Richard Huxton <dev@archonet.com>
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