Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 134C8311511 for ; Wed, 14 Dec 2011 12:06:34 -0400 (AST) Received: from sam.nabble.com ([216.139.236.26]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RarLl-0001BT-E9 for pgsql-performance@postgresql.org; Wed, 14 Dec 2011 16:06:34 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1RarLY-0002bD-0W for pgsql-performance@postgresql.org; Wed, 14 Dec 2011 08:06:20 -0800 Date: Wed, 14 Dec 2011 08:06:20 -0800 (PST) From: voodooless To: pgsql-performance@postgresql.org Message-ID: <1323878780005-5074907.post@n5.nabble.com> In-Reply-To: References: Subject: Re: Partitions and joins lead to index lookups on all partitions MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -0.8 (/) X-Archive-Number: 201112/118 X-Sequence-Number: 45917 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-tp5055965p5074907.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.