Обсуждение: Performance impact of hundreds of partitions
Hi, increasing shared_buffers has improved *a lot* the number of inserts/second, so my "problem" [1] is fixed. But now I'm worried because of the sentence (Tom Lane): "The partitioning code isn't designed to scale beyond a few dozen partitions" Is it mainly a planning problem or an execution time problem? I did a (very quick) test with 3000 empty partitions, and it took 0.5 secs to do the planning for a simple select with a very simple where condition. With 300 partitions, planning takes about 30ms. That's fine for my case, as I don't expect more than 300 partitions; and I could actually wait for .5 secs more if that helps with such large tables, and I won't be doing joins. So: the "scaling" problem would be more evident in case joins were taken into account? Or there's something else I didn't get? [1] http://archives.postgresql.org/pgsql-general/2010-04/msg00611.php
Leonardo F wrote: > Is it mainly a planning problem or an execution time problem? I have here a database with a table partitioned across 400 sub-tables. I'm using a trigger-based solution with constraint exclusion. The thing that takes the longest is planning queries. I made THAT problem just go away for the most part by using cached queries (only works within the same database connection, but thats no problem for me). I also tried a rule-based partitioning, that indeed breaks down quickly performance wise. Also, the trigger is a ON INSERT AND UPDATE, and only on the main table. If your main insert/update process knows into which partition to insert, you can speed that up even more; while still beeing able to use the automated partitioning for everything else. LG Rene
On Wed, Apr 21, 2010 at 6:45 AM, Leonardo F <m_lists@yahoo.it> wrote: > "The partitioning code isn't designed to scale beyond a few dozen partitions" > > Is it mainly a planning problem or an execution time problem? > I'll bet that is related to the planning and constraint exclusion parts. I have a couple of tables split into 100 partitions, and they work extremely well. However, I was able to alter my application such that it almost always references the correct partition directly. The only times it does not is when it requires a full scan of all partitions. All inserts are direct to proper partition. In my view, it is a big win to partition large tables such that each partition holds no more than 5 million rows. This keeps the indexes small, and the query engine can quite easily skip huge hunks of them on many queries. Also, reindexes can be done pretty quickly and in my case without seriously disrupting the application -- each table reindexes in under 5 or 10 seconds. When this was all one table, a reindex op would lock up the application for upwards of two hours.
Vick Khera wrote: > On Wed, Apr 21, 2010 at 6:45 AM, Leonardo F <m_lists@yahoo.it> wrote: > > "The partitioning code isn't designed to scale beyond a few dozen partitions" > > > > Is it mainly a planning problem or an execution time problem? > > > > I'll bet that is related to the planning and constraint exclusion > parts. I have a couple of tables split into 100 partitions, and they > work extremely well. Keep in mind that 100 is only 8 dozen ... -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
> The thing that takes the longest is planning queries. I made THAT problem just > go away for the most part by using cached queries (only works within the same > database connection, but thats no problem for me). What do you mean by "cached queries"? Prepared statements?