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
Import Notes
Reply to msg id not found:
"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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general