Re: Thoughts on statistics for continuously advancing columns

On Wed, 30 Dec 2009 11:16:45 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I don't have a better idea at the moment :-(
>
>> It's been a while since I've been bitten by this issue -- the last
>> time was under Sybase. The Sybase suggestion was to either add
>> "dummy rows" [YUCK!] to set the extreme bounds or to "lie to the
>> optimizer" by fudging the statistics after each generation. Perhaps
>> we could do better by adding columns for high and low bounds to
>> pg_statistic. These would not be set by ANALYZE, but
>> user-modifiable to cover exactly this problem? NULL would mean
>> current behavior?
>
> Well, the problem Josh has got is exactly that a constant high bound
> doesn't work.
>
> What I'm wondering about is why he finds that re-running ANALYZE
> isn't an acceptable solution. It's supposed to be a reasonably
> cheap thing to do.

What makes ANALYZE cheap is that two things:

1. It uses read only bandwidth (for the most part), which is the most
bandwidth we have
2. It doesn't take a lock that bothers anything

On the other hand ANALYZE also:

1. Uses lots of memory
2. Lots of processor
3. Can take a long time

We normally don't notice because most sets won't incur a penalty. We got a
customer who
has a single table that is over 1TB in size... We notice. Granted that is
the extreme
but it would only take a quarter of that size (which is common) to start
seeing issues.

>
> I think the cleanest solution to this would be to make ANALYZE
> cheaper, perhaps by finding some way for it to work incrementally.
>

That could be interesting. What about a running statistics set that has
some kind of
threshold? What I mean is, we run our normal analyze but we can mark a
table "HOT"
(yeah bad term). If we mark the table HOT statistics are generated on the
fly for
the planner and updated every X interval. Perhaps then written out at a
checkpoint?

This is just off the top of my head.

JD

> regards, tom lane

--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997