Updating histogram_bounds after a delete

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux