> 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.
Sure, thanks Imre
Thanks and Regards,
Ravi Garg
On Sunday, 23 February, 2020, 09:49:00 pm IST, Imre Samu
<pella.s...@gmail.com> wrote:
> ... 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 performancesee
https://stackoverflow.com/questions/29880083/postgresql-uuid-type-performanceimho:
it should be better.
best, Imre
Ravi Garg <ravi.g...@yahoo.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
<pry...@telsasoft.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
