Re: PostgreSQL 11 higher Planning time on Partitioned table

> ... txid character varying(36) NOT NULL,
> ... WHERE txnid = 'febd139d-1b7f-4564-a004-1b3474e51756'
> There is only one index (unique index btree) on 'txnID' (i.e. transaction
ID) character varying(36). Which we are creating on each partition.

IF txnid is real UUID , then you can test the
https://www.postgresql.org/docs/11/datatype-uuid.html performance
see
https://stackoverflow.com/questions/29880083/postgresql-uuid-type-performance
imho: it should be better.

best,
Imre

Ravi Garg <ravi(dot)garg(at)yahoo(dot)com> ezt írta (időpont: 2020. febr. 23., V,
11:57):

> Hi Justin,
>
> Thanks for response.
>
> Unfortunately we will not be able to migrate to PG12 any time soon.
>
> - There is only one index (unique index btree) on 'txnID' (i.e.
> transaction ID) character varying(36). Which we are creating on each
> partition.
> - Our use case is limited to simple selects (we don't join with the
> other tables) however, we are expecting ~70 million records inserted
> per day and there would be couple of updates on each records where average
> record size would be ~ 1.5 KB.
> - Currently we are thinking to have Daily partitions and as we need to
> keep 6 months of data thus 180 Partitions.However we have liberty to reduce
> the number of partitions to weekly/fortnightly/monthly, If we get
> comparable performance.
> - We need to look current partition and previous partition for all of
> our use-cases/queries.
>
> Can you please suggest what sort of combinations/partition strategy we can
> test considering data-volume/vacuum etc. Also let me know if some of the
> pg_settings can help us tuning this (I have attached my pg_settings).
>
>
> Thanks and Regards,
> Ravi Garg,
> Mob : +91-98930-66610
>
>
> On Sunday, 23 February, 2020, 03:42:13 pm IST, Justin Pryzby <
> pryzby(at)telsasoft(dot)com> wrote:
>
>
> On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wrote:
> > Hi,
> > I am looking to Range Partition one of my table (i.e. TransactionLog) in
> PostgreSQL 11.While evaluating query performance difference between the
> un-partitioned and partitioned table I am getting huge difference in
> planning time. Planning time is very high on partitioned table.Similarly
> when I query by specifying partition name directly in query the planning
> time is much less **0.081 ms** as compared to when I query based on
> partition table (parent table) name in query, where planning time **6.231
> ms** (Samples below).<br>
>
> That's probably to be expected under pg11:
>
> https://www.postgresql.org/docs/11/ddl-partitioning.html
> |Too many partitions can mean longer query planning times...
> |It is also important to consider the overhead of partitioning during
> query planning and execution. The query planner is generally able to handle
> partition hierarchies with up to a few hundred partitions fairly well,
> provided that typical queries allow the query planner to prune all but a
> small number of partitions. Planning times become longer and memory
> consumption becomes higher as more partitions are added
>
>
> > There are around ~200 child partitions. Partition pruning
> enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu,
> compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
>
>
> How large are the partitions and how many indexes each, and how large are
> they?
> Each partition will be stat()ed and each index will be open()ed and read()
> for
> every query. This was resolved in pg12:
> https://commitfest.postgresql.org/21/1778/
>
> --
> Justin
>
>