Re: [PERFORM] How to remove a table statistics ?

2012-02-03 Thread Robert Haas 
On Tue, Jan 31, 2012 at 2:36 PM, Marc Mamin m.ma...@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 is fully 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] How to remove a table statistics ?

2012-01-31 Thread Marc Mamin 
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: [PERFORM] How to remove a table statistics ?

2012-01-31 Thread Josh Berkus 
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: [PERFORM] How to remove a table statistics ?

2012-01-31 Thread Marc Mamin 
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