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

On Tue, Jun 2, 2009 at 9:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>
> delete from pg_statistic
> where (starelid, staattnum) in
> (select attrelid, attnum from pg_attribute
> where attrelid = 'my_relation'::regclass and attname = 'my_attribute');
>
> regclass knows about schemas and search paths, so stuff like
> 'my_schema.my_relation'::regclass will work unsurprisingly.
>
>
Thanks! That's very helpful.

Hey, while I have you on the line, might you be so kind as to explain why
this query is so slow? Shouldn't it just fetch the first row in the table?
What could be faster than that?

explain analyze select * from maindb_astobject limit 1;
>
> QUERY
> PLAN
>
> ------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..0.04 rows=1 width=78) (actual time=8091.962..8091.965
> rows=1 loops=1)
> -> Seq Scan on maindb_astobject (cost=0.00..3358190.12 rows=75426912
> width=78) (actual time=8091.955..8091.955 rows=1 loops=1)
> Total runtime: 8092.040 ms
> (3 rows)

The query runs perfectly fast, on the other hand, if I encourage it to use
an index like so:

explain analyze select * from maindb_astobject order by id limit 1;
>
>
> QUERY
> PLAN
>
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..0.10 rows=1 width=78) (actual time=0.203..0.205 rows=1
> loops=1)
> -> Index Scan using maindb_astobject_pkey on maindb_astobject
> (cost=0.00..7650690.77 rows=75426912 width=78) (actual time=0.196..0.196
> rows=1 loops=1)
> Total runtime: 0.292 ms
> (3 rows)
>

|>ouglas