Re: Insert large number of records

On 20 September 2017 at 22:55, Job <Job(at)colliniconsulting(dot)it> 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.