pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to remove a table statistics ?
4+ messages / 3 participants
[nested] [flat]
* How to remove a table statistics ?
@ 2012-01-31 11:50 Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 4+ messages in thread
From: Marc Mamin @ 2012-01-31 11:50 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to remove a table statistics ?
@ 2012-01-31 18:44 Josh Berkus <josh@agliodbs.com>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2012-01-31 18:44 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to remove a table statistics ?
@ 2012-01-31 19:36 Marc Mamin <M.Mamin@intershop.de>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Marc Mamin @ 2012-01-31 19:36 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-performance
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 >= 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to remove a table statistics ?
@ 2012-02-03 21:56 Robert Haas <robertmhaas@gmail.com>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 4+ messages in thread
From: Robert Haas @ 2012-02-03 21:56 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-02-03 21:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-31 11:50 How to remove a table statistics ? Marc Mamin <M.Mamin@intershop.de>
2012-01-31 18:44 ` Josh Berkus <josh@agliodbs.com>
2012-01-31 19:36 ` Marc Mamin <M.Mamin@intershop.de>
2012-02-03 21:56 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox