Re: Fwd: Relatively high planner overhead on partitions?

Hello

2013/7/19 Skarsol <skarsol(at)gmail(dot)com>:
> 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).

if all data in your test living in memory - then bottleneck is in CPU
- and any other node in execution plan is significant.

It is not surprise, because OLTP relation databases are not well
optimized for this use case. A designers expected much more
significant impact of IO operations, and these databases are designed
to minimize bottleneck in IO - with relative low memory using. This
use case is better solved in OLAP databases (read optimized databases)
designed after 2000 year - like monetdb, verticadb, or last year cool
db HANA.

Regards

Pavel

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