Postgres: FETCH vs limit

There are various of articles out there which talks about how to use (FETCH/limit). Also, you can find information on the internet about their histories.

I have used FETCH and limit both and also was able to use them to serve the same purpose/query result.

Since, I have choices and can use anyone of these. I decided to do a quick comparison ,so that I could have a reason about picking any one clause above another.

I created a table with around 200,000 records and executed a FETCH and limit query on it to get the same results. Here’s my `limit` query:

select * from employee2 order by id offset 2 rows limit 10;

And below are the execution timings:

Execution time with limit for 200,000 records

The reason I have taken `average of last 4` executions as well because usually first execution is longer since results aren’t cached.

Now, next query I executed with FETCH:

select * from employee2 order by id offset 2 rows fetch first 10 row only;

And, below are the execution timings:

Execution time with FETCH for 200,000 records

Now, you can see that FETCH is winning the race.

However, since difference is really small — I was still not sure. So, this time I did the same test against 5,000,000 records and here’re the results:

FETCH vs limit comparison

Now, you can clearly see the difference between overall execution timings. FETCH was returning results faster than `limit` and execution time difference is getting bigger as we keep adding more records.