On Tue, Jun 2, 2009 at 9:52 AM, Tom Lane
<tgl@sss.pgh.pa.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
