I tried sending this a couple days ago but I wasn't a member of the group so I think it's in limbo. Apologies if a 2nd copy shows up at some point.
We recently migrated a 1.3TB database from 8.4 to 9.2.2 on a new server. As part of this migration we added partitions to the largest tables so we could start removing old data to an archive database. Large queries perform much better due to not hitting the older data as expected. Small queries served from records in memory are suffering a much bigger performance hit than anticipated due to the partitioning. I'm able to duplicate this issue on our server trivially with these commands: http://pgsql.privatepaste.com/7223545173 Running the queries from the command line 10k times (time psql testdb < test1.sql >/dev/null) results in a 2x slowdown for the queries not using testtable_90 directly. (~4s vs ~2s). Running a similar single record select on a non-partitioned table averages 10k in 2s. Running "select 1;" 10k times in the same method averages 1.8 seconds. This matches exactly what I'm seeing in our production database. The numbers are different, but the 2x slowdown persists. Doing a similar test on another table on production with 7 children and 3 check constraints per child results in a 3x slowdown. I'm aware that partitioning has an impact on the planner, but doubling the time of in memory queries with only 5 partitions with 1 check each is much greater than anticipated. Are my expectations off and this is normal behavior or is there something I can do to try and speed these in memory queries up? I was unable to find any information online as to the expected planner impact of X # of partitions. Database information follows: Red Hat Enterprise Linux Server release 6.4 (Santiago) Linux hostname.domainname 2.6.32-358.6.1.el6.x86_64 #1 SMP Fri Mar 29 16:51:51 EDT 2013 x86_64 x86_64 x86_64 GNU/Linux PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit Server info: 4x Intel(R) Xeon(R) CPU E5-4620 0 @ 2.20GHz 128gb RAM DateStyle | ISO, MDY | configuration file default_statistics_target | 5000 | configuration file default_text_search_config | pg_catalog.english | configuration file effective_cache_size | 64000MB | configuration file effective_io_concurrency | 2 | configuration file fsync | on | configuration file lc_messages | C | configuration file lc_monetary | C | configuration file lc_numeric | C | configuration file lc_time | C | configuration file max_connections | 500 | configuration file max_stack_depth | 2MB | environment shared_buffers | 32000MB | configuration file synchronous_commit | on | configuration file TimeZone | CST6CDT | configuration file wal_buffers | 16MB | configuration file wal_level | archive | configuration file wal_sync_method | fdatasync | configuration file
