From: Matthew Hall <mhall@mhcomputing.net>
To: Rick Otten <rottenwindfish@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Would SSD improve Index Only Scan performance by a lot?
Date: Tue, 8 Oct 2019 20:17:13 -0700
Message-ID: <1483EB08-4FBB-46CE-9920-7FF12285C326@mhcomputing.net> (raw)
In-Reply-To: <CAMAYy4+xLkq3GU1-TOKDd7pRkbMvBQKeei4fbK=FuHoO=7ra-g@mail.gmail.com>
References: <CAFoK1axtgKBb-K4ssJ5fiAZgX=abX2Eq-e0m+zXfFifNyg0P5g@mail.gmail.com>
<CAMAYy4+xLkq3GU1-TOKDd7pRkbMvBQKeei4fbK=FuHoO=7ra-g@mail.gmail.com>
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 Tue, Oct 8, 2019 at 7:37 PM Arya F <arya6000@gmail.com> wrote:
>> As my table has gotten bigger, it takes longer 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 have a much faster seek time than traditional HDDs.
>>
>> Would switching to an SSD improve "Index Only Scan" time greatly? by at least 3-4 times?
>
> *If* your query is disk I/O bound, SSD can help a lot.
>
> 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.
>
> Depending on the data type and size of the data you may be able to help your 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.
>
>
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: mhall@mhcomputing.net, rottenwindfish@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Would SSD improve Index Only Scan performance by a lot?
In-Reply-To: <1483EB08-4FBB-46CE-9920-7FF12285C326@mhcomputing.net>
* 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