Date: Fri, 8 Feb 2019 07:04:44 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: keith anderson <kwj.anderson@yahoo.co.uk>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Partitioning Optimizer Questions and Issues
Message-ID: <20190208130444.GC29720@telsasoft.com>
References: <1233297695.414916.1549624431535.ref@mail.yahoo.com>
 <1233297695.414916.1549624431535@mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <1233297695.414916.1549624431535@mail.yahoo.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Fri, Feb 08, 2019 at 11:13:51AM +0000, keith anderson wrote:
> So to summarise the findings/questions from above:
> - It seems like the Postgres optimizer sometimes uses the partition level=
 statistics, and sometimes the global table level statistics? Or is it usin=
g something else?- With partitioning tables with unique identifier and retr=
ieving explicitly on those identifiers, at present the optimizer will alway=
s understimate the selectivity and overestimate the rows returned. This ina=
ccuracy increases in proportion to the number of partitions.- As a result, =
when joining to other tables, you are liable to hitting sequential scans. T=
his becomes more likely as you have more partitions or if join to more part=
itioned tables (note I am aware I could try and tune random_page_cost to tr=
y and prevent this).- To me in the examples queries described above, it mak=
es sense to use the partition statistics for the partition level access str=
ategy, but the global statistics when estimating the actual rows returned b=
y all the individual partition queries. Is there a reason not to do this? O=
r do others believe the optimizer is doing the right thing here?
> And then some general questions:
> - How do other people use partitioning but without a significant performa=
nce disadvantage on reading the data? Is there something else I should be d=
oing here to achieve the same thing without the overhead? At present my rea=
ds have increased optimization cost (as it needs to optimize access to each=
 partition) and also execution cost (access the index on every partition). =
Even without the optimizer issues described above, the cost of reading simp=
le data is extremely high relative to non-partitioned data (unless you use =
the partition key as a filter for each table to eliminate those partitions)=
=2E- Is there any chance/plan to add global indexes to postgres? If so woul=
d that impact significantly the cost of the partition drop e.g. to clean up=
 the index.
> Thanks in advance for any feedback/support,

An equality or IN() query will use the pg_stats most-common-values list,
whereas a range query will use the histogram.

The tables probably doesn't have complete MCV list.  By default, that's lim=
ited
to 100 entries.  Since the maximum allowed by ALTER..SET STATISTICS is 10k,=
 I
don't think it'll help to change it (at least for your production use case).
Each partition's rowcount appears to be estimated from its ndistinct, and n=
ot
=66rom its content, so each is estimated as having about the same rowcount.

Your partitions are sharing a sequence for their ID column, which causes the
DEFAULT IDs to be unique...but their global uniqueness isn't enforced nor
guaranteed.

Note, in postgres11, it's possible to create an index on the parent table.
It's NOT a global index, but it can be unique if it includes the partition =
key.
I don't know how closely your example describes your real use case, but I d=
on't
think that helps with your example; it doesn't seems useful to partition on=
 a
serial column.

You seem to be adding unnecessary CHECK constraints that duplicate the
partition bounds.  Note, it's still useful to include CHECK constraints on =
key
column if you're planning on DETACHing and re-ATTACHing the partitions, in
order to avoid seqscan to verify tuples don't violate specified bounds.

You might need to rethink your partitioning scheme - you should choose one =
that
causes performance to improve, and probably naturally includes the partition
key in most queries.

Perhaps you'd use 2 levels of partitioning: a RANGE partition by date, which
allows for archiving, and a HASH partition by ID, which allows for partition
pruning.  Note that it's also possible to partition on multiple keys, like
RANGE(id,date) - I don't think that's useful here, though.  PG11 also allow=
s a
"default" partition.

Or perhaps you could partition by RANGE(date) but add CHECK constraints on =
ID,
after the table is fully populated, to optimize queries by allowing for
partition pruning.

Or you could maybe change the ID column to include the timestamp (like BIND
zonesfiles YYYYMMDDNNNNNNNN).  You'd set a bigint sequence on each partitio=
n's
ID as default to the beginning of the month.  A bigint is enough to handle
5*10^4 times your volume: 20190401000020111222.  (I think this is trying to=
 be
unnecessarily clever, unless there's some reason the other two ideas don't
work.)

Justin