Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: Re: Postgresql 12, 512 partition by hash. Slow select
From: Neil <neil@fairwindsoft.com>
In-Reply-To: 
 <CAFoK1azuLEF=Q_wmFgXcxo5bkiG_06VzD18svk9gBNCR+YEqeg@mail.gmail.com>
Date: Sun, 5 Apr 2020 15:48:49 -0500
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-performance@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <4C8D8AB5-A545-4965-BA52-FC33D0C88A0F@fairwindsoft.com>
References: 
 <CAFoK1axr_T6nB8ZAq8g2QBcqv_pE=dsZsxyjatz8Q67k1VKAnw@mail.gmail.com>
 <31605.1586112900@sss.pgh.pa.us>
 <CAFoK1azuLEF=Q_wmFgXcxo5bkiG_06VzD18svk9gBNCR+YEqeg@mail.gmail.com>
To: Arya F <arya6000@gmail.com>
Precedence: bulk


> On Apr 5, 2020, at 2:50 PM, Arya F <arya6000@gmail.com> wrote:
>=20
> The table at some point will have more than 1 billion rows, the
> information stored is international residential addresses. Trying to
> figure out a way of spreading the data fairly evenly thought out
> multiple partitions, but I was unable to come up with a way of
> splitting the data so that Postgres does not have to search though all
> the partitions.
>=20


If you have to use partitions, I would split it by country using =
population for analysis. I understand that address and population are =
different, but I would expect some correlation.=20

The largest 14 countries each have a population of 100 million or more =
and represent about 62% of the world population. That means the rest of =
the world should fit easily into another 14 partitions.=20

It seems like it could be fairly easily evened out with a little bit of =
data analysis.

You could probably refine this to be no more than 20 partitions.

Now China and India could be a problem and need to be split, but I would =
not do that unless necessary. China and India both have 6 nationally =
recognized regions that could be used if needed.

Neil
-
Fairwind Software
https://www.fairwindsoft.com