pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWould SSD improve Index Only Scan performance by a lot?
4+ messages / 4 participants
[nested] [flat]
* Would SSD improve Index Only Scan performance by a lot?
@ 2019-10-08 23:37 Arya F <arya6000@gmail.com>
2019-10-09 00:12 ` Re: Would SSD improve Index Only Scan performance by a lot? Rick Otten <rottenwindfish@gmail.com>
2019-10-09 19:54 ` Re: Would SSD improve Index Only Scan performance by a lot? Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Arya F @ 2019-10-08 23:37 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Would SSD improve Index Only Scan performance by a lot?
2019-10-08 23:37 Would SSD improve Index Only Scan performance by a lot? Arya F <arya6000@gmail.com>
@ 2019-10-09 00:12 ` Rick Otten <rottenwindfish@gmail.com>
2019-10-09 03:17 ` Re: Would SSD improve Index Only Scan performance by a lot? Matthew Hall <mhall@mhcomputing.net>
1 sibling, 1 reply; 4+ messages in thread
From: Rick Otten @ 2019-10-09 00:12 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Would SSD improve Index Only Scan performance by a lot?
2019-10-08 23:37 Would SSD improve Index Only Scan performance by a lot? Arya F <arya6000@gmail.com>
2019-10-09 00:12 ` Re: Would SSD improve Index Only Scan performance by a lot? Rick Otten <rottenwindfish@gmail.com>
@ 2019-10-09 03:17 ` Matthew Hall <mhall@mhcomputing.net>
0 siblings, 0 replies; 4+ messages in thread
From: Matthew Hall @ 2019-10-09 03:17 UTC (permalink / raw)
To: Rick Otten <rottenwindfish@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Would SSD improve Index Only Scan performance by a lot?
2019-10-08 23:37 Would SSD improve Index Only Scan performance by a lot? Arya F <arya6000@gmail.com>
@ 2019-10-09 19:54 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2019-10-09 19:54 UTC (permalink / raw)
To: Arya F <arya6000@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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.
>
>
Is this in a probabilistic sense, they take longer on average, or has every
single access gotten slower? If the increase in size has caused the index
leaf pages to go from being almost entirely in cache to almost entirely not
being in cache, the slow down would probably be a lot more 3 to 4 fold.
But maybe you went from 100% in cache, to 90% in cache and 10% out of cache
(with a 40 fold slowdown for those ones), coming out to 4 fold slow down on
average. If that is the case, maybe you can get the performance back up by
tweaking some settings, rather than changing hardware.
> 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 drive access is truly the bottleneck on every single execution, then
yes, probably far more than 3-4 times.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-10-09 19:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-08 23:37 Would SSD improve Index Only Scan performance by a lot? Arya F <arya6000@gmail.com>
2019-10-09 00:12 ` Rick Otten <rottenwindfish@gmail.com>
2019-10-09 03:17 ` Matthew Hall <mhall@mhcomputing.net>
2019-10-09 19:54 ` Jeff Janes <jeff.janes@gmail.com>
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