Обсуждение: ANALYZE after CREATE TABLE AS SELECT...

Semyon Reyfman wrote:

> When I create a table with "CREATE TABLE name AS SELECT." statement and
> immediately afterward use this new table in a query does it make sense to
> run ANALYZE on the table in between?  It appears that postgres should be
> able to collect very detailed statistics while emitting the table but I am
> not sure if this is done.

It isn't.  It also doesn't create any indexes, which you might want to
do before analyze.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2/26/15 5:23 PM, Alvaro Herrera wrote:
> Semyon Reyfman wrote:
>
>> When I create a table with "CREATE TABLE name AS SELECT." statement and
>> immediately afterward use this new table in a query does it make sense to
>> run ANALYZE on the table in between?  It appears that postgres should be
>> able to collect very detailed statistics while emitting the table but I am
>> not sure if this is done.
>
> It isn't.  It also doesn't create any indexes, which you might want to
> do before analyze.

Is it necessary to create indexes before analyzing?

I usually do, just to be safe, but I thought statistics were based
solely on sampling of the heap.

--
- David Steele
david@pgmasters.net

David Steele <david@pgmasters.net> writes:
> On 2/26/15 5:23 PM, Alvaro Herrera wrote:
>> It isn't.  It also doesn't create any indexes, which you might want to
>> do before analyze.

> Is it necessary to create indexes before analyzing?

> I usually do, just to be safe, but I thought statistics were based
> solely on sampling of the heap.

ANALYZE pays no attention to plain indexes.  If you have any expression
indexes, it will collect stats for those expressions.

            regards, tom lane