Обсуждение: ANALYZE after CREATE TABLE AS SELECT...
Hi,
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 new 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.
Thanks
Simon
"Semyon Reyfman" <sreyfman@therealauthority.com> writes:
> 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 new table in between?
Yes.
regards, tom lane
On 2/26/15 2:05 PM, Tom Lane wrote: > "Semyon Reyfman" <sreyfman@therealauthority.com> writes: >> 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 new table in between? > > Yes. Yes. And to be more specific - vacuum cannot see tables that have been created in a transaction, so you should always analyze tables that you intend to read in the same transaction where you created them. Even if you do commit before reading there is a race condition with vacuum, so it's best to analyze. Bonus tip: the same is true for any temp tables you might create. More so, since vacuum will never see them at all. -- - David Steele david@pgmasters.net
Вложения
This is exactly my situation. Thanks. Semyon Reyfman -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Steele Sent: Thursday, February 26, 2015 8:19 PM To: Tom Lane; Semyon Reyfman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ANALYZE after CREATE TABLE AS SELECT... On 2/26/15 2:05 PM, Tom Lane wrote: > "Semyon Reyfman" <sreyfman@therealauthority.com> writes: >> 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 new table in between? > > Yes. Yes. And to be more specific - vacuum cannot see tables that have been created in a transaction, so you should always analyze tables that you intend to read in the same transaction where you created them. Even if you do commit before reading there is a race condition with vacuum, so it's best to analyze. Bonus tip: the same is true for any temp tables you might create. More so, since vacuum will never see them at all. -- - David Steele david@pgmasters.net