From: | Isak Hansen <isak(dot)hansen(at)gmail(dot)com> |
---|---|
To: | Douglas Alan <darkwater42(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How can I manually alter the statistics for a column? |
Date: | 2009-06-02 09:43:19 |
Message-ID: | 6b9e1eb20906020243j1cf6a914yf51bdecfda119722@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jun 1, 2009 at 8: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.
>
Assuming estimates are off, wouldn't increasing the stats target be a
better approach? Then adjust the planner cost constants, in particular
seq_page_cost, random_page_cost and effective_cache_size, to prevent a
seq scan?
Considering you've pulled off the workaround, you must know what
you're doing, but I'd be worried about it backfiring in the future.
Regards,
Isak
> 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.
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Maxim Boguk | 2009-06-02 11:58:28 | Issue with Inheritance and Partitioning and grants |
Previous Message | Daniel Schuchardt | 2009-06-02 08:54:29 | Re: Foreign Key question |