[GENERAL] Partitioning with the index on the master table.
I have a list-based partition table and I want to prevent scanning of all children tables in a JOIN, which is using the partition column. I know that CHECK constraints are not used in this case so I hope that creating an index on the partition column would help. But I am not sure if I should create this index on the master table or on each of children tables. It seems kind of strange to create an index on a children table where all values of the partition column are the same. So will an index on the master table be sufficient in this case? Thanks, Simon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
[GENERAL] 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 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
Re: [GENERAL] ANALYZE after CREATE TABLE AS SELECT...
This is exactly my situation. Thanks. Semyon Reyfman -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@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 sreyf...@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 da...@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
