Skip site navigation (1) Skip section navigation (2)

Re: How can I manually alter the statistics for a column?

From: Douglas Alan <darkwater42(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How can I manually alter the statistics for a column?
Date: 2009-06-02 07:09:55
Message-ID: ce6334d00906020009y1a495ac5hd35aafe3c2246125@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)gmail(dot)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.
>

In response to

Responses

pgsql-general by date

Next:From: Anton MarchenkovDate: 2009-06-02 07:38:56
Subject: Order by parameter inside pgsql function ignored
Previous:From: Hiroshi InoueDate: 2009-06-02 03:22:13
Subject: Re: [GENERAL] trouble with to_char('L')

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group