Re: avoiding nested loops when joining on partitioned tables

On Sun, Oct 31, 2010 at 6:35 PM, Peter Neal <doabackflip(at)gmail(dot)com> wrote:
> Is there any way I can explain this to postgres? When I query the parent
> table of the partitions,  "SELECT * from A, B where a.id=b.id;", the planner
> does a sequential scan on A, A1, A2, ... an index scan on B, B1, B2, ...
> then a nested loop, which generally takes a while.
>

The index scan on the B tables should be very quick to discount the
tables which have no matching data. It will take I expect exactly one
page of the index to determine that. Assuming you have plenty of RAM,
those pages should remain in your memory and not cause any disk I/O
after the first such iteration.

> As I say, I presume this is because the planner does not know that there is
> no overlap in 'id' values between the different partitions - is there any
> way to express this?

I don't believe there is. If the inside loop is using an index scan
on each partition, that's about as good as you can do.