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 stats
* 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 very
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
data
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 special
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
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 the
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 saying
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 be
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 setting
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 to
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 capability.
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 the
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
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 --
though
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
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 (creating
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
--
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 whether
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 makes it higher
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, whose
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
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
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 ...
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
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 to EXPLAIN
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
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 the
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
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
EXPLAIN
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
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 hypothetical
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.
It
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 results,
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
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 that EXPLAIN
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.
--
--Josh
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 the
37 matches
Mail list logo
