Обсуждение: avoiding nested loops when joining on partitioned tables

Hi,

I have two tables (A and B), which are partitioned (A1, A2... B1, B2...) for easy deletion of old records. They are linked by a bigint column "id", which is defined as a foreign key in each B partition referencing the corresponding A partition. Many rows in B1 can reference a single row in A1. The "id" column is indexed in both tables. Each partition could have >1million rows.

The id column in each A partition gets its nextval from a (common) counter, and the inserts always use the default value for this column - I know that B1 references rows in A1 only, B2 -> A2 only etc.

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.

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?

Thanks,


Pete

ps please copy me on replies as I am not subscribed.

On Sun, Oct 31, 2010 at 6:35 PM, Peter Neal <doabackflip@gmail.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.

On 2010-10-31, Peter Neal <doabackflip@gmail.com> wrote:
> --0016363b85c479ce9d0493f14f93
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi,
>
> I have two tables (A and B), which are partitioned (A1, A2... B1, B2...) for
> easy deletion of old records. They are linked by a bigint column "id", which
> is defined as a foreign key in each B partition referencing the
> corresponding A partition. Many rows in B1 can reference a single row in A1.
> The "id" column is indexed in both tables. Each partition could have
>>1million rows.
>
> The id column in each A partition gets its nextval from a (common) counter,
> and the inserts always use the default value for this column - I know that
> B1 references rows in A1 only, B2 -> A2 only etc.

> Is there any way I can explain this to postgres?

 ALTER TABLE ONLY B1 ADD FOREIGN KEY (id) REFERENCES A1(id);
 ALTER TABLE ONLY B2 ADD FOREIGN KEY (id) REFERENCES A2(id);
 ALTER TABLE ONLY B3 ADD FOREIGN KEY (id) REFERENCES A3(id);
    ...

> 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.

> 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?

Constraint exclusion probably isn't able to help with that query as you're
asking for every row.  (in any ase check that constraint exclusion is
set to 'on', or 'partition' - sql:"show constraint exclusion;")

As your design seems fairly common you may find that newer (possily
future) major versions of postgres can do constraint exclusion on FK
constraints as well as on check constraints

for now you may need to rewrite your query:

SELECT * from A1, B1 where a1.id=b1.id
union all
SELECT * from A2, B2 where a2.id=b2.id;
union all
...

This can be done programatically (using plpgsql and execute for example)
"union all" will be much faster than plain "union" as no uniqueness
checks are done and they are not needed.

--
ɹǝpun uʍop ɯoɹɟ sƃuıʇǝǝɹ⅁