Re: avoiding nested loops when joining on partitioned tables

On 2010-10-31, Peter Neal <doabackflip(at)gmail(dot)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ıʇǝǝɹ⅁