Hello,
Some more tests have shown that removing the statistics just move the
performance issue to other places.
The main issue here is a bad design, so I'd better focus on this than losing
too much time with the current situation.
But this raises an interesting question on how/where does Postgres store
statistics on functional indexes.
in pg_statistics there are information on the column content, but I couldn't
find stats on the function result which is fully computed only during the index
creation.
I guess that the planner would need to know at least the function cost to
weight the benefit of such an index.
In my case I would set the function cost to 200 ...
I have also tried to reduce random_page_cost to 2, and it seems to help in a
few cases.
(anonymized)
explain analyze
SELECT min(msoffset) as t, coalesce(pipelinecall_id,-2) as pid
from aserrorlist_20120125 l
WHERE 1 = 1
AND msoffset = 132750300
AND my_func('foo',20120125,l.id, l.header_9_10_id, l.categories_id,
l.firstline_id) @@ to_aserrcfg_search_tsq($KUKU$lexeme_1 ! lexeme_2$KUKU$)
group by
ridcount,pipelinecall_id,coalesce(toplevelrid,msoffset::varchar);
without stats: http://explain.depesz.com/s/qPg
with stats: http://explain.depesz.com/s/88q
aserr_20120125_tvi: GIN Index on my_func(.,.,.,.,.,.)
best regards,
Marc Mamin
-Original Message-
From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-
ow...@postgresql.org] On Behalf Of Josh Berkus
Sent: Dienstag, 31. Januar 2012 19:44
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to remove a table statistics ?
On 1/31/12 3:50 AM, Marc Mamin wrote:
Hello,
I have a weird table, upon with the queries are much faster when no
statics were collected.
Is there a way to delete statistics information for a table ?
I've tried ALTER.. set STATISTICS 0 and then run ANALYZE, but it
seems
that old statistics are kept this way.
Can I delete entries directly in pg_statistic ?
(Postgresql 9.1)
You can, but it won't do any good; autovaccum will replace them.
It would be better to fix the actual query plan issue. If you can,
post
the query plans with and without statistics (EXPLAIN ANALYZE, please)
here.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-
performa...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
