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.