Drop any statistics of table after it's truncated

Lists: pgsql-hackers
From: Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Drop any statistics of table after it's truncated
Date: 2014-10-14 13:32:10
Message-ID: CAD21AoBhE7pX2cxUNNAHZv3RO5LmoW6UqeW3pbQLwOr+-RCM1A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi all,

I found that the statistics are still remained after it's truncated.
In addition, the analyzing ignores table does not have any tuple.
After table truncated, the entry of statistics continues to remain
unless insertion and analyzing are executed.
There is reason why statistics are remained?

Attached patch is one line patch adds RemoveStatistics() into
ExecuteTruncate(), to remove statistics entry of table.

--
Regards,

-------
Sawada Masahiko

Attachment Content-Type Size
Drop_statistics_after_truncated_v1.patch application/octet-stream 488 bytes
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Drop any statistics of table after it's truncated
Date: 2014-10-14 14:20:15
Message-ID: 31233.1413296415@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com> writes:
> I found that the statistics are still remained after it's truncated.
> In addition, the analyzing ignores table does not have any tuple.
> After table truncated, the entry of statistics continues to remain
> unless insertion and analyzing are executed.
> There is reason why statistics are remained?

Yes, actually, that's intentional. The idea is that once you start
loading data into the table, it's most likely going to look something
like the old data; therefore, the stale statistics are better than
none at all. Eventually auto-ANALYZE will replace the stats,
but until that happens, it seems best to keep using the old stats.
(Of course there are counterexamples to this, but removing the stats
is bad in more cases than not.)

> Attached patch is one line patch adds RemoveStatistics() into
> ExecuteTruncate(), to remove statistics entry of table.

-1, for the reasons explained above.

regards, tom lane

From: Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Drop any statistics of table after it's truncated
Date: 2014-10-14 15:16:28
Message-ID: CAD21AoC++RtZA8_Tve7MB62_XxmX3jckzWVTiLQeeucLOSbigQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, Oct 14, 2014 at 11:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com> writes:
>> I found that the statistics are still remained after it's truncated.
>> In addition, the analyzing ignores table does not have any tuple.
>> After table truncated, the entry of statistics continues to remain
>> unless insertion and analyzing are executed.
>> There is reason why statistics are remained?
>
> Yes, actually, that's intentional. The idea is that once you start
> loading data into the table, it's most likely going to look something
> like the old data; therefore, the stale statistics are better than
> none at all. Eventually auto-ANALYZE will replace the stats,
> but until that happens, it seems best to keep using the old stats.
> (Of course there are counterexamples to this, but removing the stats
> is bad in more cases than not.)
>
>> Attached patch is one line patch adds RemoveStatistics() into
>> ExecuteTruncate(), to remove statistics entry of table.
>
> -1, for the reasons explained above.
>

I understood that reason.
Thank you for explaining!

--
Regards,

-------
Sawada Masahiko