4

The two queries below seem equivalent. Is one better than the other? e.g. in terms of performance.

select * from some_table offset 10 limit 10;
select * from some_table offset 10 fetch next 10 rows;
2
  • Check the execution plan
    – user1822
    Commented Mar 1, 2019 at 14:21
  • 2
    The biggest difference: the second one is standard SQL, the first one is not
    – user1822
    Commented Mar 1, 2019 at 14:25

1 Answer 1

3

Is one better than the other? e.g. in terms of performance.

No, they just differ in syntax.

Both achieve pagination, with the same exact performance.

The first statement uses the LIMIT clause, which is not part of standard SQL and originates as a vendor SQL extension. The second statement uses the FETCH clause, which was first introduced in the official SQL:2008 standard.

With links to official documentation, here's a table showing support for each statement in various major database engines:

Database Engine First statement (LIMIT) Second statement (FETCH NEXT)
MySQL 8.0.33 Supported Not supported
PostgreSQL 15.3 Supported Supported
Microsoft SQL Server 2022 Not supported Supported
Oracle 19c Not supported Not supported
SQLite 3.42.0 Supported Not supported

You can validate the performance aspect yourself using a sample PostgreSQL database and comparing the execution plans:

CREATE TABLE some_table (
  id SERIAL PRIMARY KEY,
  column1 TEXT,
  column2 INTEGER,
  column3 FLOAT,
  column4 DATE
);

INSERT INTO some_table (column1, column2, column3, column4)
SELECT 
  md5(random()::text)::TEXT,
  floor(random() * 100)::INTEGER,
  random()::FLOAT,
  CURRENT_DATE - (random() * 365)::INTEGER
FROM generate_series(1, 100);

EXPLAIN ANALYZE SELECT * FROM some_table OFFSET 10 LIMIT 10;
EXPLAIN ANALYZE SELECT * FROM some_table OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

The output will show the exact same execution plan for both:

postgres=# EXPLAIN ANALYZE SELECT * FROM some_table OFFSET 10 LIMIT 10;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.30..0.60 rows=10 width=53) (actual time=0.014..0.015 rows=10 loops=1)
   ->  Seq Scan on some_table  (cost=0.00..3.00 rows=100 width=53) (actual time=0.011..0.012 rows=20 loops=1)
 Planning Time: 2.478 ms
 Execution Time: 0.044 ms
(4 rows)


postgres=# EXPLAIN ANALYZE SELECT * FROM some_table OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.30..0.60 rows=10 width=53) (actual time=0.012..0.013 rows=10 loops=1)
   ->  Seq Scan on some_table  (cost=0.00..3.00 rows=100 width=53) (actual time=0.010..0.011 rows=20 loops=1)
 Planning Time: 0.051 ms
 Execution Time: 0.020 ms
(4 rows)

The first one is visualised here and the second one is visualised here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.