Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to remove a table statistics ?

How to remove a table statistics ?

From
"Marc Mamin"
Date:

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)

 short backgroud Info:

 

 One of the table index is a GIN on a tsvector returning function, which is very costy.

 once analyzed, the query planner often ignore this index in favour of other one, hence triggering this function too often.

 

 I'll fix that model, but am first looking for a quick way to restore performance on our production servers.

 

 

 best regards,

 

 Marc Mamin

Re: How to remove a table statistics ?

From
Josh Berkus
Date:
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

Re: How to remove a table statistics ?

From
"Marc Mamin"
Date:
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

Re: How to remove a table statistics ?

From
Robert Haas
Date:
On Tue, Jan 31, 2012 at 2:36 PM, Marc Mamin <M.Mamin@intershop.de> wrote:
> 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
isfully computed only during the index creation. 

Look for rows where starelid is equal to the OID of the index.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company