From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | Bill Moran <wmoran(at)potentialtech(dot)com> |
Cc: | Vick Khera <vivek(at)khera(dot)org>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: What's a reasonable maximum number for table partitions? |
Date: | 2015-02-13 22:27:52 |
Message-ID: | CAGuHJrNfN6BpfU7FmbmGRELZWEfag5GS7h2U6PO6hM68AqvHSg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)potentialtech(dot)com>
wrote:
> On Sat, 14 Feb 2015 11:14:10 +1300
> Tim Uckun <timuckun(at)gmail(dot)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 <vivek(at)khera(dot)org> wrote:
> >
> > >
> > > On Thu, Feb 12, 2015 at 7:44 PM, Tim Uckun <timuckun(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2015-02-13 22:33:05 | Re: [HACKERS] question on Postgres smart shutdown mode |
Previous Message | Bill Moran | 2015-02-13 22:15:57 | Re: What's a reasonable maximum number for table partitions? |