On Mon, Oct 15, 2012 at 12:06:27AM +0100, Greg Stark wrote:
On Sat, Oct 13, 2012 at 3:13 AM, Stephen Frost sfr...@snowman.net 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
On Sat, Oct 13, 2012 at 3:49 PM, Joshua Berkus j...@agliodbs.com 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
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
On Sat, Oct 13, 2012 at 3:49 PM, Joshua Berkus j...@agliodbs.com 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
On Sat, Oct 13, 2012 at 3:13 AM, Stephen Frost sfr...@snowman.net 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
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
complicated
On Sat, Oct 13, 2012 at 12:49 PM, Joshua Berkus j...@agliodbs.com 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
Joshua Berkus j...@agliodbs.com 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
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 air.
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
Josh Berkus j...@agliodbs.com 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
* 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 a
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 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
[ 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, or
* 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 likely
On Oct 12, 2012, at 22:13, Stephen Frost sfr...@snowman.net 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
* 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
18 matches
Mail list logo
