SQL语句 中 BETWEEN AND 和 LIMIT OFFSET 效率问题

Question

postgesql 数据库中 表tbl_a 共500000条数据，150个字段
如下SQl语句 耗时1.135秒

    SELECT
      * 
    FROM
      tbl_a 
    WHERE
      seq_no between 490000 and 500000
    ORDER BY
      seq_no 

如下SQl语句 耗时1.355秒

    SELECT
      * 
    FROM
       tbl_a
    ORDER BY
      seq_no 
    LIMIT
       10000 OFFSET 490000

上面两个sql语句分别左联多个不同的表

SELECT
    *
FROM
  ( 
    SELECT
      * 
    FROM
      tbl_a 
    WHERE
      seq_no between 490000 and 500000
    ORDER BY
      seq_no 
  ) di 
  LEFT JOIN ( 
    ...
  ）on ...
  LEFT JOIN ( 
    ...
  ）on ...

SELECT
    *
FROM
  ( 
    SELECT
      * 
    FROM
      tbl_a 
    ORDER BY
      seq_no 
    LIMIT
       10000 OFFSET 490000
  ) di 
  LEFT JOIN ( 
    ...
  ）on ...
  LEFT JOIN ( 
    ...
  ）on ...

between and 的耗时 2.619
limit offset的耗时2.013
想问为什么 limit offset的效率比between and高

Answer 1

SELECT
      * 
    FROM
      tbl_a 
WHERE seq_no >= 490000
    ORDER BY
      seq_no
LIMIT
       10000 OFFSET 490000