Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iI2TZ-00014W-Oj for pgsql-performance@arkaria.postgresql.org; Wed, 09 Oct 2019 03:17:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iI2TY-0008GX-KC for pgsql-performance@arkaria.postgresql.org; Wed, 09 Oct 2019 03:17:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iI2TY-0008GM-BG for pgsql-performance@lists.postgresql.org; Wed, 09 Oct 2019 03:17:20 +0000 Received: from master.mhcomputing.net ([74.208.228.170] helo=mail.mhcomputing.net) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iI2TW-0004mR-2V for pgsql-performance@lists.postgresql.org; Wed, 09 Oct 2019 03:17:19 +0000 Received: from [10.10.1.151] (unknown [68.255.153.162]) by mail.mhcomputing.net (Postfix) with ESMTPSA id 69047132; Tue, 8 Oct 2019 20:17:15 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-53CDA478-AAA1-4A76-AD6F-338A8E924A11 Mime-Version: 1.0 (1.0) Subject: Re: Would SSD improve Index Only Scan performance by a lot? From: Matthew Hall X-Mailer: iPhone Mail (16G102) In-Reply-To: Date: Tue, 8 Oct 2019 20:17:13 -0700 Cc: pgsql-performance@lists.postgresql.org Content-Transfer-Encoding: 7bit Message-Id: <1483EB08-4FBB-46CE-9920-7FF12285C326@mhcomputing.net> References: To: Rick Otten List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail-53CDA478-AAA1-4A76-AD6F-338A8E924A11 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable For indexes the SSDs are at least 4X faster but you won't get that to happen= unless you fix the planner tunable for the random page fetch cost first. Su= per important change for SSDs.=20 Matthew Hall > On Oct 8, 2019, at 5:12 PM, Rick Otten wrote: >=20 >=20 >> On Tue, Oct 8, 2019 at 7:37 PM Arya F wrote: >> As my table has gotten bigger, it takes longer to get a single row back w= hen querying a row by its btree index. >>=20 >> Right now the database is running on a traditional HDD. SSDs have a much f= aster seek time than traditional HDDs.=20 >>=20 >> Would switching to an SSD improve "Index Only Scan" time greatly? by at l= east 3-4 times? >=20 > *If* your query is disk I/O bound, SSD can help a lot. >=20 > If your data is already in memory, or file system cache, and your query is= bound by CPU or bloated/corrupted indexes, or some query inefficiency, then= faster disks really won't do anything. >=20 > Depending on the data type and size of the data you may be able to help yo= ur query performance by choosing an index type other than the out-of-the-box= btree as well (such as a hash or brin index) or maybe even a different sort= order on the index, or a partial index. >=20 >=20 --Apple-Mail-53CDA478-AAA1-4A76-AD6F-338A8E924A11 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable For indexes the SSDs are at least 4X faster= but you won't get that to happen unless you fix the planner tunable for the= random page fetch cost first. Super important change for SSDs. 
Matthew Hall

On Oct 8, 2019, at 5:12 PM, Rick Otten <rottenwindfish@gmail.com> wrote:


=
On Tu= e, Oct 8, 2019 at 7:37 PM Arya F <a= rya6000@gmail.com> wrote:
As my table has gotten bigger, it takes longe= r to get a single row back when querying a row by its btree index.
=
Right now the database is running on a traditional HDD. SSDs h= ave a much faster seek time than traditional HDDs. 

Would switching to an SSD improve "Index Only Scan" time greatly? by a= t least 3-4 times?

*If* your que= ry is disk I/O bound, SSD can help a lot.

If your d= ata is already in memory, or file system cache, and your query is bound by C= PU or bloated/corrupted indexes, or some query inefficiency, then faster dis= ks really won't do anything.

Depending on the data t= ype and size of the data you may be able to help your query performance by c= hoosing an index type other than the out-of-the-box btree as well (such as a= hash or brin index) or maybe even a different sort order on the index, or a= partial index.


= --Apple-Mail-53CDA478-AAA1-4A76-AD6F-338A8E924A11--