From: voodooless <cwillemsen@technocon.com>
To: pgsql-performance@postgresql.org
Subject: Re: Partitions and joins lead to index lookups on all partitions
Date: Wed, 14 Dec 2011 08:06:20 -0800 (PST)
Message-ID: <1323878780005-5074907.post@n5.nabble.com> (raw)
In-Reply-To: <zarafa.4edf8328.2dfa.0b8fae0b2af83bae@meel.technocon.local>
References: <zarafa.4edf8328.2dfa.0b8fae0b2af83bae@meel.technocon.local>
Back again,
I did some tests with our test machine, having a difficult query doing some
fancy stuff ;)
I made two versions, one using partitioned data, one, using unpartitioned
data, both having the same equivalent indexes. It's using two of those big
tables, one 28GB data and 17GB index, one 25GB data and 41GB indexes (both
for the unpartitioned versions). Our test machine has 32GB of memory, short
config:
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
effective_cache_size = 22GB
work_mem = 80MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 7680MB
max_connections = 400
At first I tested the query performance. It turned out that the
unpartitioned version was about 36 times faster, of course for the obvious
reason stated in my initial post. both are fully using the indexes they
have, and the partitioned version even has it's indexes on SSD.
Then I did some insert tests using generate_series to insert 100000 rows
into one of the tables. It turns out that the unpartitioned version is again
faster, this time 30.9 vs 1.8 seconds. This is a huge difference. For the
second table, with the huge 41GB index it's 30.5 vs 5.2 seconds, still a big
difference.
Conclusion: partitioning does not benefit us, and probably others, specially
when doing lots of joins and using parameterized queries.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Partitions-and-joins-lead-to-index-lookups-on-all-partitions...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cwillemsen@technocon.com
Subject: Re: Partitions and joins lead to index lookups on all partitions
In-Reply-To: <1323878780005-5074907.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox