From: | Skarsol <skarsol(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Fwd: Relatively high planner overhead on partitions? |
Date: | 2013-07-19 13:52:00 |
Message-ID: | CAMt8e=GZRH5T33f-hxesCKujzfWXmOJKXX2=une1mxG_QmH9Ng@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2013-07-19 16:16:30 | Re: Fwd: Relatively high planner overhead on partitions? |
Previous Message | Stefan Keller | 2013-07-19 13:38:55 | Re: FTS performance issue - planner problem identified (but only partially resolved) |