On Mon, Oct 15, 2012 at 12:06:27AM +0100, Greg Stark wrote:
> On Sat, Oct 13, 2012 at 3:13 AM, Stephen Frost wrote:
> > Josh's concern is about autovacuum causing lots of stats churn, which is
> > understandable, we don't want it constantly rescanning a table
>
> I don't think rescanning the tabl
On Sat, Oct 13, 2012 at 3:49 PM, Joshua Berkus wrote:
> So, problem #1 is coming up with a mathematical formula. My initial target
> values are in terms of # of rows in the table vs. # of writes before analyze
> is triggered:
>
> 1 : 3
> 10 : 5
> 100 : 10
> 1000 : 100
> 10 : 2000
> 100
Robert,
> As I've said before (and I believe Simon has said similar things), I
> think we should be vacuuming the heap much more often but only doing
> index vac when we accumulate enough dead tuples to justify the cost of
> the index scan. Pruning the heap is cheap and very effective.
You are p
On Sat, Oct 13, 2012 at 3:49 PM, Joshua Berkus wrote:
> For my part, over the last 3 years of consulting and dealing with
> postgresql.conf settings for more than 140 clients:
>
> * only 10% of them ever touched the autoanalyze settings at all
> * of the ~~ 14 who did:
>* 1 improved the tunin
On Sat, Oct 13, 2012 at 3:13 AM, Stephen Frost wrote:
> Josh's concern is about autovacuum causing lots of stats churn, which is
> understandable, we don't want it constantly rescanning a table
I don't think rescanning the table is a big concern. autovacuum will
only scan as often as it feels lik
> Ah. Okay, maybe we can agree that that wasn't a good idea.
Oh, I'd say there's no question it was a mistake. We just didn't have the data
at the time to realize it.
> I don't really see that we need to bend over backwards to exactly
> match
> some data points that you made up out of thin ai
Joshua Berkus writes:
> I've been going over the notes and email archives from the period
> where Matt O'Connor and I arrived at the current settings. All of our
> testing was devoted to autovacuum, not autoanalyze.
> Our mistake was assuming that the same formula which worked well for
> vacuum w
On Sat, Oct 13, 2012 at 12:49 PM, Joshua Berkus wrote:
>
> So, problem #1 is coming up with a mathematical formula. My initial target
> values are in terms of # of rows in the table vs. # of writes before analyze
> is triggered:
>
> 1 : 3
> 10 : 5
> 100 : 10
> 1000 : 100
> 10 : 2000
> 1
> For my part, while that's certainly an interesting idea, it's far
> more
> complicated than even providing GUCs and the idea is to make PG just
> "do
> it right", not to offer the user more ways to get it wrong...
Yes, please let's not replace the existing too-simplistic knobs with giant
compl
* David Johnston (pol...@yahoo.com) wrote:
> Instead of global could you attach an interface function to the table and
> have the auto-analyzer call that function to basically ask the table whether
> it needs to be analyzed? Still need to deal with defaults and provide a
> decent supply of buil
On Oct 12, 2012, at 22:13, Stephen Frost wrote:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> [ shrug... ] You're attacking a straw man, or more precisely putting
>> words into my mouth about what the percentage-based thresholds might be.
>> Notice the examples I gave involved update percentages q
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> [ shrug... ] You're attacking a straw man, or more precisely putting
> words into my mouth about what the percentage-based thresholds might be.
> Notice the examples I gave involved update percentages quite far north
> of 100%. It's possible and maybe like
> [ shrug... ] You're attacking a straw man, or more precisely putting
> words into my mouth about what the percentage-based thresholds might be.
> Notice the examples I gave involved update percentages quite far north
> of 100%. It's possible and maybe likely that we need a sliding scale.
Yes,
Josh Berkus writes:
>> I remember having got voted down on the percentage approach back when
>> we first put AV into core, but I remain convinced that decision was a
>> bad one.
> Yeah, I was one of the ones voting against you. The reason not to have
> percentage-only is for small tables. Imagi
> No, it's not that easy. The question you have to ask is "when has that
> initial write burst stopped?". As an example, if autovacuum happened to
> see that table in the instant after CREATE, it might autovacuum it while
> it's still empty, and then this rule fails to trigger any further effort
* Josh Berkus (j...@agliodbs.com) wrote:
> Because Snowman asked me for an example:
Thanks. :)
> That's a 25X difference in execution time. This is not the first time
> I've seen this issue.
If we can figure out an 'easy' solution to this, I'd definitely vote for
it being back-patched. Having
Josh Berkus writes:
> Now, I look at this, and ask myself: why didn't autoanalyze kick in at
> step 3? After all, this was a table which had 0 rows, we inserted 45
> rows, making the table infinitely larger. It should have got on the
> autoanalyze list, no?
> Well, no. It seems that any table
All,
> 1. User creates new table
> 2. User inserts 45 records into new table.
> 3. Time passes.
> 4. User creates a query which joins against new table.
> 5. Planner uses estimate of 1000 rows for the new table.
> 6. User gets very bad query plan.
Because Snowman asked me for an example:
Before
Folks,
One chronic problem users encounter is this one:
1. User creates new table
2. User inserts 45 records into new table.
3. Time passes.
4. User creates a query which joins against new table.
5. Planner uses estimate of 1000 rows for the new table.
6. User gets very bad query plan.
Now, I lo
19 matches
Mail list logo
