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 <wmo...@potentialtech.com> wrote: > On Sat, 14 Feb 2015 11:14:10 +1300 > Tim Uckun <timuc...@gmail.com> 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 <vi...@khera.org> wrote: > > > > > > > > On Thu, Feb 12, 2015 at 7:44 PM, Tim Uckun <timuc...@gmail.com> 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 >
