This might get pretty crazy if I am doing queries like WHERE client_id in () or when I am trying to join some table with the client table. Maybe I can precalculate the ids that are going to go into each partition and set the constraint as where client_id in (some_huge_list).
On Sat, Feb 14, 2015 at 11:15 AM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:
On Sat, 14 Feb 2015 11:14:10 +1300
Tim Uckun <timuckun@xxxxxxxxx> wrote:
> If I used modulo arithmetic how would the query optimizer know which table
> to include and exclude? For example say I did modulo 100 based on the field
> client_id. I create a base table with the trigger to insert the data into
> the proper child table. Each table has the constraint (client_id % 100) = X
>
> So if I do select from base table where client_id = 10 would postgres know
> to only select from client_table_10? Normally I would always have a
> client_id in my queries so hopefully the this could be very efficient.
Unless the newest versions of PostgreSQL has improved on this, you have to
give the planner just a bit of a hint ... you're query should look like:
SELET ... WHERE client_id = 10 AND client_id % 100 = 10;
The part after the AND looks silly and redundant, but it guarantees that
the planner will consider the partition layout when it plans the query,
and in every test that I've run the result will be that the planner only
looks at the one child table.
--
> On Sat, Feb 14, 2015 at 5:12 AM, Vick Khera <vivek@xxxxxxxxx> wrote:
>
> >
> > On Thu, Feb 12, 2015 at 7:44 PM, Tim Uckun <timuckun@xxxxxxxxx> wrote:
> >
> >> Does anybody have experience with huge number of partitions if so where
> >> did you start running into trouble?
> >>
> >
> > I use an arbitrary 100-way split for a lot of tracking info. Just modulo
> > 100 on the ID column. I've never had any issues with that. If you can
> > adjust your queries to pick the right partition ahead of time, which I am
> > able to do for many queries, the number of partitions shouldn't matter
> > much. Only rarely do I need to query the primary table.
> >
> > I don't think your plan for 365 partitions is outrageous on modern large
> > hardware. For 1000 partitions, I don't know. It will depend on how you can
> > optimize your queries before giving them to postgres.
> >
Bill Moran
