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