Tom,
> We have also talked about solving the multi-column statistics problem
> (which, at its core, is "which combinations of columns are worth
> accumulating stats for?" --- you can't possibly store stats for every
> combination!) by having what would amount to hints from the DBA saying
> "keep s
Tom Lane wrote:
> We have also talked about solving the multi-column statistics problem
> (which, at its core, is "which combinations of columns are worth
> accumulating stats for?" --- you can't possibly store stats for every
> combination!) by having what would amount to hints from the DBA sayin
Casey Duncan <[EMAIL PROTECTED]> writes:
> Yes, but it may be much more efficient for the human to tell the
> computer than for the computer to introspect things. Take, for
> example, ndisinct as data grows large.
Yeah, an override estimate for a column's ndistinct seems a perfect
example of t
On Oct 12, 2006, at 4:26 AM, Andrew Sullivan wrote:
On Thu, Oct 12, 2006 at 08:34:45AM +0200, Florian Weimer wrote:
Some statistics are very hard to gather from a sample, e.g. the
number
of distinct values in a column.
Then how can the DBA know it, either? The problem with this sort of
a
On Thu, Oct 12, 2006 at 08:34:45AM +0200, Florian Weimer wrote:
>
> Some statistics are very hard to gather from a sample, e.g. the number
> of distinct values in a column.
Then how can the DBA know it, either? The problem with this sort of
argument is always that people are claiming some specia
On Wed, Oct 11, 2006 at 03:08:42PM -0700, Ron Mayer wrote:
> Is one example is the table of addresses clustered by zip-code
> and indexes on State, City, County, etc?
No.
> Now I'm not saying that a more advanced statistics system
> couldn't one-day be written that sees these patterns in the
> da
* Andrew Sullivan:
> Just because I'm one of those statistics true believers, what sort of
> information do you think it is possible for the DBA to take into
> consideration, when building a hint, that could not in principle be
> gathered efficiently by a statistics system?
Some statistics are ve
Greg Stark <[EMAIL PROTECTED]> writes:
> You would create your proposed index, then run ANALYZE and EXPLAIN to your
> heart's content. When you have it set up just so then you REINDEX your index
> and you're set.
And when you realize you don't want it after all ... you need an exclusive
lock on th
Hi,
Am 11.10.2006 um 19:39 schrieb Simon Riggs:
I'm sure everybody would be glad to see the existing work submitted
as a
Work-in-Progress patch to pgsql-patches.
Would a patch against a clean 7.4.8 source tree useful for you?
Otherwise, I had to spend some time to migrate the code to 8.2..
Tom Lane <[EMAIL PROTECTED]> writes:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > Anything that can be done to wheedle down your choices
> > before you have to run EXPLAIN ANALYZE is a bonus.
>
> Fair enough, but I prefer Peter's suggestion of attaching the
> hypothetical index definitions t
Andrew Sullivan wrote:
> Just because I'm one of those statistics true believers, what sort of
> information do you think it is possible for the DBA to take into
> consideration, when building a hint, that could not in principle be
> gathered efficiently by a statistics system? It seems to me that
Simon,
The University of North Carolina (I think?) did some nice work on not only
hypothetical indexes, but hypothetical materialized views (as well as
really materialized view planner selection). Have you looked at that
work? I think I forwarded the paper & code to Jonah at one point ...
-
On Oct 11, 2006, at 3:00 PM, Andrew Sullivan wrote:
On Wed, Oct 11, 2006 at 12:40:42PM -0400, Gregory Stark wrote:
poorly written query. In fact Oracle is going in the opposite
direction of
even relying on hints internally. Its plan stability feature
depends on
generating and storing hints
On Wed, Oct 11, 2006 at 03:27:19PM -0400, Mark Woodward wrote:
> improving the planner. Like I said, it is inarguable that there will
> always be queries that the planner can not execute efficiently based on
> the statistics gathered by analze. Since that number must be greater than
> zero, some m
On Wed, Oct 11, 2006 at 12:40:42PM -0400, Gregory Stark wrote:
> poorly written query. In fact Oracle is going in the opposite direction of
> even relying on hints internally. Its plan stability feature depends on
> generating and storing hints internally associated with every query.
But IBM, whos
> "Mark Woodward" <[EMAIL PROTECTED]> writes:
>> I would say that a "simpler" planner with better hints
>> will always be capable of creating a better query plan.
>
> This is demonstrably false: all you need is an out-of-date hint, and
> you can have a worse plan.
That doesn't make it false, it ma
"Mark Woodward" <[EMAIL PROTECTED]> writes:
> I would say that a "simpler" planner with better hints
> will always be capable of creating a better query plan.
This is demonstrably false: all you need is an out-of-date hint, and
you can have a worse plan.
The argument against hints is not about wh
>
> "Mark Woodward" <[EMAIL PROTECTED]> writes:
>
>> The analyzer, at least the last time I checked, does not recognize these
>> relationships.
>
> The analyzer is imperfect but arguing from any particular imperfection is
> weak
> because someone will just come back and say we should work on that p
On Wed, 2006-10-11 at 14:30 +0200, Kai-Uwe Sattler wrote:
> We have already an implementation of an index advisor for
> 7.4.8.
> It definitely requires some work to port it to 8.2 and to make it
> usable for production environments.
> Furthermore, there are some performance bottlenecks (crea
On Wed, 2006-10-11 at 14:30 +0200, Kai-Uwe Sattler wrote:
> sorry for opening a new thread but I have just subscribed to the
> list.
Not at all, glad to hear about your implementation.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
---(end
"Mark Woodward" <[EMAIL PROTECTED]> writes:
> The analyzer, at least the last time I checked, does not recognize these
> relationships.
The analyzer is imperfect but arguing from any particular imperfection is weak
because someone will just come back and say we should work on that problem --
th
Hi,
sorry for opening a new thread but I have just subscribed to the
list. We have already an implementation of an index advisor for
7.4.8. This is the result of several master theses, so it's no
production ready yet, but it works (with some limitations).
The main idea is:
1. to run the pl
> On 10/10/06, Mark Woodward <[EMAIL PROTECTED]> wrote:
>> I think the idea of "virtual indexes" is pretty interesting, but
>> ultimately a lesser solution to a more fundimental issue, and that would
>> be "hands on" control over the planner. Estimating the effect of an
>> index
>> on a query "prio
> The above process can be performed without tool support, but its clear
> that further automation will help greatly here. I foresee that the
> development of both server-side and tools will take more than one
> release. Discussion of tool support can begin once we have agreed
> server-side capabil
Thanks everybody for comments so far; this will be a useful discussion.
On Tue, 2006-10-10 at 18:56 -0400, Robert Treat wrote:
> On Tuesday 10 October 2006 12:06, Tom Lane wrote:
> > > Similar in usage to an EXPLAIN, the RECOMMEND command would return a
> > > list of indexes that need to be added
> > Another thing that this brings up is "hints" to a query. Over the
> > years, I have run into situation where the planner wasn't
> great. It
> > would be nice to try forcing different strategies on the
> planner and
> > see if performance caan be improved.
> >
>
> you can do this by sett
On Tue, 2006-10-10 at 20:17 -0400, Mark Woodward wrote:
> Another thing that this brings up is "hints" to a query. Over the
> years, I
> have run into situation where the planner wasn't great. It would be
> nice
> to try forcing different strategies on the planner and see if
> performance
> caan b
On 10/10/06, Mark Woodward <[EMAIL PROTECTED]> wrote:
I think the idea of "virtual indexes" is pretty interesting, but
ultimately a lesser solution to a more fundimental issue, and that would
be "hands on" control over the planner. Estimating the effect of an index
on a query "prior" to creating
Mark,
> Another thing that this brings up is "hints" to a query. Over the years,
> I have run into situation where the planner wasn't great. It would be
> nice to try forcing different strategies on the planner and see if
> performance caan be improved.
See discussion on -performance.
--
--Jos
> Simon Riggs <[EMAIL PROTECTED]> writes:
>> - RECOMMEND command
>
>> Similar in usage to an EXPLAIN, the RECOMMEND command would return a
>> list of indexes that need to be added to get the cheapest plan for a
>> particular query (no explain plan result though).
>
> Both of these seem to assume t
Robert Treat <[EMAIL PROTECTED]> writes:
> Anything that can be done to wheedle down your choices
> before you have to run EXPLAIN ANALYZE is a bonus.
Fair enough, but I prefer Peter's suggestion of attaching the
hypothetical index definitions to EXPLAIN itself, rather than making
bogus catalog
On Tuesday 10 October 2006 12:06, Tom Lane wrote:
> > Similar in usage to an EXPLAIN, the RECOMMEND command would return a
> > list of indexes that need to be added to get the cheapest plan for a
> > particular query (no explain plan result though).
>
> Both of these seem to assume that EXPLAIN res
On Tue, Oct 10, 2006 at 06:06:09PM +0200, Peter Eisentraut wrote:
> Simon Riggs wrote:
> > For 8.3, I'd like to add the following two related features to assist
> > with Index Tuning and usability:
> >
> > - Virtual Indexes
>
> This seems useful, but I'm not sure we need a catalog object for that.
Peter Eisentraut wrote:
> Simon Riggs wrote:
>> For 8.3, I'd like to add the following two related features to assist
>> with Index Tuning and usability:
>>
>> - Virtual Indexes
>
> This seems useful, but I'm not sure we need a catalog object for that.
> It might be sufficient to declare these h
Simon Riggs <[EMAIL PROTECTED]> writes:
> - Virtual Indexes
> An index which only exists in the catalog, so is visible to the planner
> but not the executor.
Say what? What would that possibly be useful for, other than crashing
any bit of code that failed to know about it?
> - RECOMMEND command
Simon Riggs wrote:
> For 8.3, I'd like to add the following two related features to assist
> with Index Tuning and usability:
>
> - Virtual Indexes
This seems useful, but I'm not sure we need a catalog object for that.
It might be sufficient to declare these hypothetical indexes within the
EXPL
For 8.3, I'd like to add the following two related features to assist
with Index Tuning and usability:
- Virtual Indexes
An index which only exists in the catalog, so is visible to the planner
but not the executor. This is useful where a specific SQL query is being
hand-tuned, allowing very spec
37 matches
Mail list logo
