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 ar
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 Wed, Aug 23, 2006 at 08:42:10AM -0700, Mark Dilger wrote:
> 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,
> >>
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 deter
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 act
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.
It
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
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 j
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 know
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... :)
>
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
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 al
"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
"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
> > 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 broadcast
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 qu
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
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 h
On Tue, Aug 15, 2006 at 07:00:49PM +0200, Peter Eisentraut wrote:
> 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
> > jo
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 ar
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 cou
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 compli
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 a
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
Ü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 figur
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
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 c
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
In article <[EMAIL PROTECTED]>,
Perez <[EMAIL PROTECTED]> wrote:
> In article <[EMAIL PROTECTED]>,
> [EMAIL PROTECTED] (Tom Lane) wrote:
>
> > Martijn van Oosterhout writes:
> > > My main problem is that selectivity is the wrong measurement. What
> > > users really want to be able to communica
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Tom Lane) wrote:
> Martijn van Oosterhout 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
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 s
(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 n
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.
"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 th
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
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.
On Tue, Aug 08, 2006 at 08:23:05AM -0700, Mark Dilger wrote:
> If this feature I'm proposing already exists, sorry for the
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
.
This big, complicated expression looks different for every query - and
current
> 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 ;
This way hints can be ad
"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
>.
> This big, complicated expression looks different for every query - and
> curr
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 postg
Tom Lane wrote:
Martijn van Oosterhout 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* chang
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
Martijn van Oosterhout 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 foreign key
> refe
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
On Tue, Aug 08, 2006 at 04:14:45PM -0400, Tom Lane wrote:
> Martijn van Oosterhout 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 w
Martijn van Oosterhout 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* changing your queries
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.
Image a query like "s
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.
> Image a query like "select ... from t1 join
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
49 matches
Mail list logo