On 20 September 2017 at 22:55, Job <Job@xxxxxxxxxxxxxxxxxxxx> wrote: > One further question: within a query launched on the MASTER table where i need to scan every table, for exaple to search rows locatd in more partitions. > In there a way to improve "parallel scans" between more table at the same time or not? > I noticed, with explain analyze, the scan in the master table is Always sequential, descending into the partitions. Since nobody has replied to your latest question yet, I'll give it a try. Which tables a query on your MASTER table needs to scan largely depends on a PG feature called "constraint exclusion". That is to say, if the query optimizer can deduce from your query that it only needs to scan certain partitions for the required results, then it will do so. Now, whether the optimizer can do that, depends on whether your query conditions contain the same (or equivalent) expressions on the same fields of the same types as your partitioning constraints. That 'same type' part is one that people easily miss. Sometimes part of an expression gets auto-cast to make it compatible with the remainder of the expression, but that is sometimes not the same type as what is used in your partitioning (exclusion) constraint. In such cases the planner often doesn't see the similarity between the two expressions and ends up scanning the entire set of partitions. See also section 5.10.4 in https://www.postgresql.org/docs/current/static/ddl-partitioning.html , although it doesn't go into details of how to construct your select statements to prevent scanning the entire partition set. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
