pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Neil <neil@fairwindsoft.com>
To: Arya F <arya6000@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql 12, 512 partition by hash. Slow select
Date: Sun, 5 Apr 2020 15:48:49 -0500
Message-ID: <4C8D8AB5-A545-4965-BA52-FC33D0C88A0F@fairwindsoft.com> (raw)
In-Reply-To: <CAFoK1azuLEF=Q_wmFgXcxo5bkiG_06VzD18svk9gBNCR+YEqeg@mail.gmail.com>
References: <CAFoK1axr_T6nB8ZAq8g2QBcqv_pE=dsZsxyjatz8Q67k1VKAnw@mail.gmail.com>
	<31605.1586112900@sss.pgh.pa.us>
	<CAFoK1azuLEF=Q_wmFgXcxo5bkiG_06VzD18svk9gBNCR+YEqeg@mail.gmail.com>


> On Apr 5, 2020, at 2:50 PM, Arya F <arya6000@gmail.com> wrote:
> 
> 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.
> 


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. 

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. 

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









view thread (5+ messages)

Message-ID: <4C8D8AB5-A545-4965-BA52-FC33D0C88A0F@fairwindsoft.com>
Permalink:  ../4C8D8AB5-A545-4965-BA52-FC33D0C88A0F@fairwindsoft.com/
Also on:    postgresql.org/message-id/4C8D8AB5-A545-4965-BA52-FC33D0C88A0F@fairwindsoft.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: neil@fairwindsoft.com, arya6000@gmail.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
  Subject: Re: Postgresql 12, 512 partition by hash. Slow select
  In-Reply-To: <4C8D8AB5-A545-4965-BA52-FC33D0C88A0F@fairwindsoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox