On Aug 17, 2006, at 1:41 PM, Peter Eisentraut wrote:
But we need to work this from the other end anyway. We need to
determine first, what sort of statistics the planner could make use of.
Then we can figure out the difficulties in collecting them.
There are still some things that the
Is there actually evidence that there's a lot of problems with bad join
orders? ISTM that's one of the areas where the planner actually does a
pretty good job.
I put together a quick demonstration using AxBxC where AxB is empty but AxC is
not. Sure enough, postgres chooses AxC first, then xB,
On Tue, Aug 22, 2006 at 11:56:17AM -0700, Mark Dilger wrote:
I proposed something like this quite a bit up-thread. I was hoping we
could have a mode in which the system would run the second, third, fourth,
... best plans rather than just the best looking one, and then determine
from actual
Jim C. Nasby wrote:
On Tue, Aug 22, 2006 at 11:56:17AM -0700, Mark Dilger wrote:
I proposed something like this quite a bit up-thread. I was hoping we
could have a mode in which the system would run the second, third, fourth,
... best plans rather than just the best looking one, and then
Peter Eisentraut wrote:
Jim C. Nasby wrote:
Meet EXPLAIN ANALYZE.
Which does no good for apps that you don't control the code on. Even
if you do control the code, you have to find a way to stick EXPLAIN
ANALYZE in front of every query, and figure out how to deal with
what's comming back.
Gregory Stark wrote:
I'm not sure it's worth throwing out the more user-friendly interface
we have now but I think it's clear that a table is the obvious
machine-readable format if you're already sitting in an SQL
database... :)
Then again, a table might not be the optimal format for an
Peter Eisentraut [EMAIL PROTECTED] writes:
Gregory Stark wrote:
I'm not sure it's worth throwing out the more user-friendly interface
we have now but I think it's clear that a table is the obvious
machine-readable format if you're already sitting in an SQL
database... :)
Then again,
On Aug 15, 2006, at 10:40 AM, Jim C. Nasby wrote:
On Mon, Aug 14, 2006 at 11:41:29PM +0300, Hannu Krosing wrote:
??hel kenal p??eval, E, 2006-08-14 kell 18:21, kirjutas Peter
Eisentraut:
Perez wrote:
I thought, from watching the list for a while, that the planner
statistics needed were
Arturo Pérez wrote:
The DBA therefore pokes the
right information into
the planner's statistical tables (or, perhaps, a more human-
manageable one that gets
compiled into the planner's stats).
I think we're perfectly capable of producing a system that can collect
the statistics. We just
Peter Eisentraut wrote:
Arturo Pérez wrote:
The DBA therefore pokes the
right information into
the planner's statistical tables (or, perhaps, a more human-
manageable one that gets
compiled into the planner's stats).
I think we're perfectly capable of producing a system that can collect
the
Jim C. Nasby [EMAIL PROTECTED] writes:
On Tue, Aug 15, 2006 at 07:55:28PM +0200, Peter Eisentraut wrote:
It would not be hard to create an auto explain analyze mode that
implicitly runs EXPLAIN ANALYZE along with every query and logs the
result. On its face, it sounds like an obviously
On Wed, Aug 16, 2006 at 06:48:09PM -0400, Gregory Stark wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
On Tue, Aug 15, 2006 at 07:55:28PM +0200, Peter Eisentraut wrote:
It would not be hard to create an auto explain analyze mode that
implicitly runs EXPLAIN ANALYZE along with every
On Mon, Aug 14, 2006 at 11:41:29PM +0300, Hannu Krosing wrote:
??hel kenal p??eval, E, 2006-08-14 kell 18:21, kirjutas Peter Eisentraut:
Perez wrote:
I thought, from watching the list for a while, that the planner
statistics needed were known but that how to gather the statistics
was
On Aug 15, 2006, at 10:40 , Jim C. Nasby wrote:
Yeah, unless someone comes up with some kind of 'magic', I think
trying
to handle every cross-column possibility is a non-starter. IIRC, that
argument is what's stalled cross-column stats every time in the
past. It
makes a lot more sense to
AgentM wrote:
I've always found it odd that database didn't determine which
statistics are the most interesting from the queries themselves.
The overhead of doing that on the fly is probably prohibitive. More
explicit profiling support could be helpful, but that would seem a lot
more
On Aug 15, 2006, at 12:26 , Peter Eisentraut wrote:
AgentM wrote:
I've always found it odd that database didn't determine which
statistics are the most interesting from the queries themselves.
The overhead of doing that on the fly is probably prohibitive. More
explicit profiling support
AgentM wrote:
Couldn't the session be explicitly transferred into a special
analysis mode? Explain analyze could run on every query implicitly
and point out time and row count discrepancies as HINTs. Multi-column
joins, for example, could be pointed out and display whether or not
there are
Jim C. Nasby wrote:
Meet EXPLAIN ANALYZE.
Which does no good for apps that you don't control the code on. Even
if you do control the code, you have to find a way to stick EXPLAIN
ANALYZE in front of every query, and figure out how to deal with
what's comming back.
It would not be hard to
On Aug 15, 2006, at 13:55 , Peter Eisentraut wrote:
Jim C. Nasby wrote:
Meet EXPLAIN ANALYZE.
Which does no good for apps that you don't control the code on. Even
if you do control the code, you have to find a way to stick EXPLAIN
ANALYZE in front of every query, and figure out how to deal
On Tue, Aug 15, 2006 at 07:55:28PM +0200, Peter Eisentraut wrote:
Jim C. Nasby wrote:
Meet EXPLAIN ANALYZE.
Which does no good for apps that you don't control the code on. Even
if you do control the code, you have to find a way to stick EXPLAIN
ANALYZE in front of every query, and
see. Collecting the statistics thereafter isn't that hard, but there
needs to be a way to not collect an exponential volume of statistics on
all column combinations.
You could collect them on all FK relationships - is that enough?
Chris
---(end of
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
see. Collecting the statistics thereafter isn't that hard, but there
needs to be a way to not collect an exponential volume of statistics on
all column combinations.
You could collect them on all FK relationships - is that enough?
As
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] (Jim C. Nasby) wrote:
On Wed, Aug 09, 2006 at 08:31:42AM -0400, Perez wrote:
Every once in a while people talk about collecting better statistics,
correlating multi-column correlations etc. But there never seems to be
a way to collect
Perez wrote:
I thought, from watching the list for a while, that the planner
statistics needed were known but that how to gather the statistics
was not?
I think over the course of the discussion we have figured out that we
would like to have cross-column correlation statistics. The precise
Ühel kenal päeval, E, 2006-08-14 kell 18:21, kirjutas Peter Eisentraut:
Perez wrote:
I thought, from watching the list for a while, that the planner
statistics needed were known but that how to gather the statistics
was not?
I think over the course of the discussion we have figured out
In article [EMAIL PROTECTED],
Perez [EMAIL PROTECTED] wrote:
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] (Tom Lane) wrote:
Martijn van Oosterhout kleptog@svana.org writes:
My main problem is that selectivity is the wrong measurement. What
users really want to be able to
On Wed, Aug 09, 2006 at 08:31:42AM -0400, Perez wrote:
Every once in a while people talk about collecting better statistics,
correlating multi-column correlations etc. But there never seems to be
a way to collect that data/statistics.
Would it be possible to determine the additional
On Tue, 2006-08-08 at 22:14, Tom Lane wrote:
So some kind of override for statistical guesses doesn't seem completely
silly to me. But it needs to be declarative information that's stored
somewhere out of view of the actual SQL queries. IMHO anyway.
The real problem is that sometimes there's
Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
ISTM theat the easiest way would be to introduce a sort of predicate
like so:
SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1);
The one saving grace of Florian's proposal was that you could go hack
the
On Wed, Aug 09, 2006 at 12:57:39PM +0200, Florian G. Pflug wrote:
Fixing the generic problem is surely the best _if_ there is a fix for
the generic problem at all. But if your where-conditions involves fields
from 10 different tables, then IMHO there is no way to _ever_ guarantee
that postgres
Florian G. Pflug [EMAIL PROTECTED] writes:
Image a complex, autogenerated query with looks something like this
select
from t1
join t2 on ...
join t3 on ...
join t4 on ...
...
...
where
big, complicated expression derived from some user input.
This big, complicated expression
SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1);
ISTM that you introduced the Oracle silliness again, putting the hint into the
query.
My suggestion would be to tell about it separately. Something like
CREATE HINT FOR JOIN foo, bar ON foo.a=bar.b AS some hint;
This way hints
Tom Lane wrote:
Florian G. Pflug [EMAIL PROTECTED] writes:
Image a complex, autogenerated query with looks something like this
select
from t1
join t2 on ...
join t3 on ...
join t4 on ...
...
...
where
big, complicated expression derived from some user input.
This big, complicated
On Wed, Aug 09, 2006 at 02:02:10PM +0200, Martijn van Oosterhout wrote:
On Wed, Aug 09, 2006 at 12:57:39PM +0200, Florian G. Pflug wrote:
Fixing the generic problem is surely the best _if_ there is a fix for
the generic problem at all. But if your where-conditions involves fields
from 10
Jim C. Nasby [EMAIL PROTECTED] writes:
On Wed, Aug 09, 2006 at 02:02:10PM +0200, Martijn van Oosterhout wrote:
Perhaps the way to go would be to allow users to declare columns often
used together and have ANALYSE collect information on correlation which
can be used later...
One thing that
Jim C. Nasby wrote:
Been suggested before... the problem is actually doing something useful
with all that data that's collected, as well as how to collect it
without greatly impacting the system.
Identifying the best plan by means of actually running multiple plans and timing
them is useful.
(Not sure how we'd implement that, seeing that ANALYZE currently works
on one table at a time, but it's probably doable --- and it'd fix the
fundamental problem for correlation statistics, which is how not to try
to collect stats about an exponential number of combinations ...)
An exponential
On Wed, Aug 09, 2006 at 03:33:21PM -0400, Joshua Reich wrote:
(Not sure how we'd implement that, seeing that ANALYZE currently works
on one table at a time, but it's probably doable --- and it'd fix the
fundamental problem for correlation statistics, which is how not to try
to collect stats
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] (Tom Lane) wrote:
Martijn van Oosterhout kleptog@svana.org writes:
My main problem is that selectivity is the wrong measurement. What
users really want to be able to communicate is:
1. If you join tables a and b on x, the number of
If this feature I'm proposing already exists, sorry for the waste of bandwidth,
and could someone please point me to it? :)
What if there were a mode that told postgres to do an exhaustive search (or if
not exhaustive, then much more extensive search) of all plans (or many plans),
trying each
On Tue, Aug 08, 2006 at 01:55:35PM +0200, Florian G. Pflug wrote:
Hi
Since the discussion about how to force a specific plan has
come up, I though I'd post an idea I had for this a while ago.
It's not reall well though out yet, but anyway.
snip
Image a query like select ... from t1 join
Martijn van Oosterhout wrote:
On Tue, Aug 08, 2006 at 01:55:35PM +0200, Florian G. Pflug wrote:
Hi
Since the discussion about how to force a specific plan has
come up, I though I'd post an idea I had for this a while ago.
It's not reall well though out yet, but anyway.
snip
Image a query
Martijn van Oosterhout kleptog@svana.org writes:
ISTM theat the easiest way would be to introduce a sort of predicate
like so:
SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1);
The one saving grace of Florian's proposal was that you could go hack
the statistics *without*
On Tue, Aug 08, 2006 at 04:14:45PM -0400, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
ISTM theat the easiest way would be to introduce a sort of predicate
like so:
SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1);
The one saving grace of Florian's
Tom Lane wrote:
The thing I object to about the I want to decorate my queries with
planner hints mindset is that it's coming at it from the wrong
direction. You should never be thinking in terms of fix this one
query, because that just leads back into the same dead end that your
fix doesn't
Martijn van Oosterhout kleptog@svana.org writes:
My main problem is that selectivity is the wrong measurement. What
users really want to be able to communicate is:
1. If you join tables a and b on x, the number of resulting rows will be
the number of roows selected from b (since b.x id a
46 matches
Mail list logo
