Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id CB00CF172E1 for ; Wed, 4 Jul 2012 02:44:04 -0300 (ADT) Received: from mail-pb0-f46.google.com ([209.85.160.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SmINe-0004b6-5D for pgsql-performance@postgresql.org; Wed, 04 Jul 2012 05:44:03 +0000 Received: by pbbrp8 with SMTP id rp8so10485463pbb.19 for ; Tue, 03 Jul 2012 22:43:49 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:x-gm-message-state; bh=YCVOTwGd+OBHwwy7DIm7L/o0i5CZB0LsaQYWVq0KslU=; b=I6kotUYmyVFrTj/IFlERTLdHf6WPNN98KV3219hR0CFsws1DpREwE7uPLrVtrEWgx1 Em3gp5q5FaR1Jarp59C/jRokSdP7LiW1ia5eCiS3rUSGk0nBk+N9VAewty8gH9+hr4rO xQUjHYDeRADeEWfFDtstQexGR6U8C2rxYQMWJDJvAKe0ssVkxyU6IFH9OPTOGAzMQEaj 3llyoPpoDUbt7ppT+9bjzU7k4c9GsdFcZzpgIjYJvYAwT89XOYd/WeJWDfN7uZWxfFmn 5B2k5IYgtVu3WCZl69qdbdUnIcuCX77s+Qm3xkf62ITJMg/ftfo7iAKwTC2FkQYIErb2 DzvQ== Received: by 10.68.226.65 with SMTP id rq1mr7506198pbc.25.1341380628851; Tue, 03 Jul 2012 22:43:48 -0700 (PDT) Received: from ayaki.localdomain (office1.postnewspapers.com.au. [150.101.171.128]) by mx.google.com with ESMTPS id qp9sm17025911pbc.9.2012.07.03.22.43.45 (version=SSLv3 cipher=OTHER); Tue, 03 Jul 2012 22:43:48 -0700 (PDT) Message-ID: <4FF3D80F.9040408@ringerc.id.au> Date: Wed, 04 Jul 2012 13:43:43 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20120605 Thunderbird/13.0 MIME-Version: 1.0 To: Reza Taheri CC: "pgsql-performance@postgresql.org" , Robert Haas Subject: Re: The need for clustered indexes to boost TPC-V performance References: <66CE997FB523C04E9749452273184C6C137CB88A86@exch-mbx-113.vmware.com> In-Reply-To: <66CE997FB523C04E9749452273184C6C137CB88A86@exch-mbx-113.vmware.com> Content-Type: multipart/alternative; boundary="------------060909090806090606050103" X-Gm-Message-State: ALoCoQkhQpyoJ+YLeaD3k0v/WuBSSq+YX92/7QlY38TE1J228rIRq6UsX6Z3zINg8P1RgLElwE04 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/12 X-Sequence-Number: 47193 This is a multi-part message in MIME format. --------------060909090806090606050103 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 07/04/2012 07:13 AM, Reza Taheri wrote: > > Following the earlier email introducing the TPC-V benchmark, and that > we are developing an industry standard benchmarking kit for TPC-V > using PostgreSQL, here is a specific performance issue we have run into. > Which version of PostgreSQL are you using? How has it been tuned beyond the defaults - autovacuum settings, shared_buffers, effective_cache_size, WAL settings, etc? How much RAM is on the blade? What OS and version are on the blade? > Comparing the table sizes, we are close to 2X larger (more on this in > a later note). But the index size is what stands out. Our overall > index usage (again, after accounting for different numbers of rows) is > 4.8X times larger. 35% of our I/Os are to the index space. I am > guessing that the 4.8X ballooning has something to do with this, and > that in itself explains a lot about our high I/O rate, as well as > higher CPU/tran cycles compared to MS SQL (we are 2.5-3 times slower). > This is making me wonder about bloat issues and whether proper vacuuming is being done. If the visibility map and free space map aren't maintained by proper vaccum operation everything gets messy, fast. > Well, MS SQL used a "clustered index" for CT, i.e., the data is held > in the leaf pages of the index B-Tree. The data and index are in one > data structure. Once you lookup the index, you also have the data at > zero additional cost. > > [snip] > > Is the PGSQL community willing to invest in a feature that a) has been > requested by many others already; and b) can make a huge difference in > a benchmark that can lend substantial credibility to PGSQL performance? > while PostgreSQL doesn't support covering indexes or clustered indexes at this point, 9.2 has added support for index-only scans, which are a half-way point of sorts. See: http://rhaas.blogspot.com.au/2011/10/index-only-scans-weve-got-em.html http://rhaas.blogspot.com.au/2010/11/index-only-scans.html http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a2822fb9337a21f98ac4ce850bb4145acf47ca27 If at all possible please see how your test is affected by this PostgreSQL 9.2 enhancement. It should make a big difference, and if it doesn't it's important to know why. (CC'd Robert Haas) I'm not sure what the best option for getting a 9.2 beta build for Windows is. As for the "invest" side - that's really a matter for EnterpriseDB, Command Prompt, Red Hat, and the other backers who're employing people to work on the DB. Consider asking on pgsql-hackers, too; if nothing else you'll get a good explanation of the current state and progress toward clustered indexes. Some links that may be useful to you are: http://wiki.postgresql.org/wiki/Todo Things that it'd be good to support/implement at some point. Surprisingly, covering/clustered indexes aren't on there or at least aren't easily found. It's certainly a much-desired feature despite its apparent absence from the TODO. http://wiki.postgresql.org/wiki/PostgreSQL_9.2_Development_Plan http://wiki.postgresql.org/wiki/PostgreSQL_9.2_Open_Items -- Craig Ringer --------------060909090806090606050103 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 07/04/2012 07:13 AM, Reza Taheri wrote:

Following the earlier email introducing the TPC-V benchmark, and that we are developing an industry standard benchmarking kit for TPC-V using PostgreSQL, here is a specific performance issue we have run into.


Which version of PostgreSQL are you using?

How has it been tuned beyond the defaults - autovacuum settings, shared_buffers, effective_cache_size, WAL settings, etc?

How much RAM is on the blade? What OS and version are on the blade?

Comparing the table sizes, we are close to 2X larger (more on this in a later note). But the index size is what stands out. Our overall index usage (again, after accounting for different numbers of rows) is 4.8X times larger. 35% of our I/Os are to the index space. I am guessing that the 4.8X ballooning has something to do with this, and that in itself explains a lot about our high I/O rate, as well as higher CPU/tran cycles compared to MS SQL (we are  2.5-3 times slower).

This is making me wonder about bloat issues and whether proper vacuuming is being done. If the visibility map and free space map aren't maintained by proper vaccum operation everything gets messy, fast.

Well, MS SQL used a “clustered index” for CT, i.e., the data is held in the leaf pages of the index B-Tree. The data and index are in one data structure. Once you lookup the index, you also have the data at zero additional cost.

[snip]

Is the PGSQL community willing to invest in a feature that a) has been requested by many others already; and b) can make a huge difference in a benchmark that can lend substantial credibility to PGSQL performance?


while PostgreSQL doesn't support covering indexes or clustered indexes at this point, 9.2 has added support for index-only scans, which are a half-way point of sorts. See:

  http://rhaas.blogspot.com.au/2011/10/index-only-scans-weve-got-em.html
  http://rhaas.blogspot.com.au/2010/11/index-only-scans.html
  http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a2822fb9337a21f98ac4ce850bb4145acf47ca27

If at all possible please see how your test is affected by this PostgreSQL 9.2 enhancement. It should make a big difference, and if it doesn't it's important to know why.

(CC'd Robert Haas)

I'm not sure what the best option for getting a 9.2 beta build for Windows is.


As for the "invest" side - that's really a matter for EnterpriseDB, Command Prompt, Red Hat, and the other backers who're employing people to work on the DB. Consider asking on pgsql-hackers, too; if nothing else you'll get a good explanation of the current state and progress toward clustered indexes.

Some links that may be useful to you are:

  http://wiki.postgresql.org/wiki/Todo
  Things that it'd be good to support/implement at some point. Surprisingly, covering/clustered indexes aren't on there or at least aren't easily found. It's certainly a much-desired feature despite its apparent absence from the TODO.

  http://wiki.postgresql.org/wiki/PostgreSQL_9.2_Development_Plan
  http://wiki.postgresql.org/wiki/PostgreSQL_9.2_Open_Items

--
Craig Ringer

--------------060909090806090606050103--