SQL - Get specific row without a full table scan

Question

I'm using Postgresql (cockroachdb) and I want to select a specific row. For example, there are thousands of records and I want to select row number 999.

In this case we would use LIMIT and OFFSET, SELECT * FROM table LIMIT 1 OFFSET 998;

However, using LIMIT and OFFSET can cause performance issue according to this post. So I'm wondering if there a way to get specific row without a full table scan.

I feel like it is possible because the database seems to sort data by primary key, that when I do SELECT * FROM table; it always show a sorted result. Since it is sorted by primary key, database can use index to access a specific row, right?

And so can you, by specifying WHERE PrimaryKey = Value. But that doesn't give the database a fast way of jumping to the row that, if we numbered them sequentially, would be number 999, no more than you could quickly find word number 999 in the dictionary, despite it being sorted. (You could think of tricks to get there fast anyway -- those tricks actually do translate somewhat to databases.) — Jeroen Mostert

Alex Robinson Alex Robinson · Accepted Answer · 2018-04-01T01:01:17

If you select rows based on the primary key (e.g. SELECT * FROM table WHERE <primary key> = <value>), no scans will be needed underneath the hood. The same is also true if you define a secondary index on the table and apply a WHERE clause that filters based on the column(s) in the secondary index.

SQL - Get specific row without a full table scan

1 Answers