> ... 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.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 > >
