Re: Updating histogram_bounds after a delete

Oh, I'm using 8.2

On Wed, Mar 16, 2011 at 3:40 PM, Derrick Rice <derrick(dot)rice(at)gmail(dot)com>wrote:

> Greetings.
>
> I recently ran into a problem with a planner opting for a sequential scan
> rather than a bitmap heap scan because the stats suggested that my delete
> query was going to affect 33% of the rows, rather than the 1% it really
> was. I was able to follow the planner's logic and came to the realization
> that it was a result of the histogram_bounds for that column being out of
> date.
>
> The table is regularly purged of some of it's oldest data, and new data is
> constantly added. It seems to me that PostgreSQL *should* be able to
> identify a query which is going to delete all rows within a histogram
> bucket, and could possibly react by updating the histogram_bounds at
> commit-time, rather than needing an additional analyze or needing
> auto-analyze settings jacked way up.
>
> Alternatively, it might be nice to be able to manually describe the table
> (I've been following the "no hints" discussion) by providing information
> along the lines of "always assume that column event_date is uniformly
> distributed". This would be provided as schema information, not additional
> SQL syntax for hints.
>
> Is this something that is remotely feasible, has the suggestion been made
> before, or am I asking for something where a solution already exists?
>
> Thanks,
>
> Derrick
>