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
fullycomputed 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 >= 1327503000000
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-owner@postgresql.org [mailto:pgsql-performance-
> owner@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-
> performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
