If you want something done right, I guess you have to do it yourself!
Here's the answer to my question. It works great! Or so it seems to:
delete from pg_statistic s
where exists ( select 1
from pg_class as c, pg_attribute as a
where a.attrelid = c.relfilenode
and s.starelid = c.relfilenode
and s.staattnum = a.attnum
and c.relname = 'maindb_astobject'
and attname = 'survey_id'
);
In the above SQL statement, "maindb_astobject" is the name of the table and "survey_id" is the name of the column. The statement deletes all the statistics for the specified column in the specified table.
|>ouglas
On Mon, Jun 1, 2009 at 2:20 PM, Douglas Alan
<darkwater42@gmail.com> wrote:
I'd like to manually alter the statistics for a column, as for the column in question the statistics are causing Postgres to do the wrong thing for my purposes. (I.e., a Seq Scan, rather than an Index Scan.) If someone can tell me how to achieve this, I would quite grateful.
Thanks!
|>ouglas
P.S. Actually, for this particular problem, just deleting the statistics would be fine. I've tried doing:
alter table maindb_astobject alter column survey_id set statistics 0;
And then analyzing the column, but when "statistics" for a column are set to 0, Postgres seems to leave the current statistics in place, which is not the right thing for me at all. I can successfully set "statistics" to 1, but that turns out to be one statistic too many.
I've tried settings the statistics via the table "pg_stats", but that turns out to be a view, and Postgres won't allow to me to alter it.
Perhaps I can achieve the end by altering the "pg_statistic" table instead, but that table is more than a bit opaque to me.
P.P.S The Seq Scan is 2-4 orders of magnitude slower than the Index Scan.