PostgreSQL limit的ties子句

　　给定如下数据：

id	name	salary
1	Alice	2000
2	Bob	3000
3	Charlie	2000
4	David	2000
5	Eddie	1500

SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 2 ROWS ONLY;

 返回前两行数据，如下：

id	name	salary
2	Bob	3000
1	Alice	2000

　　如果希望salary=2000的记录全部返回，可以使用TIES子句（结果好像不太对？），

SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 2 ROWS WITH TIES;
其结果如下：

id	name	salary
2	Bob	3000
1	Alice	2000
3	Charlie	2000
4	David	2000

　　否则就要使用DENSE_RANK分析函数，如下：

SELECT
    c,
    DENSE_RANK() OVER (
        ORDER BY desc
    ) dense_rank_number
FROM
    t
where dense_rank_number <= n  // n为前几名

zjh@postgres=# explain analyze select * from (select a.*,dense_rank() over(order by name desc) dense_rank_number from movies a) x where x.dense_rank_number<=3;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on x  (cost=461344.97..551344.97 rows=1000000 width=28) (actual time=6119.130..9667.407 rows=900 loops=1)
   Filter: (x.dense_rank_number <= 3)
   Rows Removed by Filter: 2999100
   ->  WindowAgg  (cost=461344.97..513844.97 rows=3000000 width=28) (actual time=6119.126..9341.719 rows=3000000 loops=1)
         ->  Sort  (cost=461344.97..468844.97 rows=3000000 width=20) (actual time=6118.989..6604.120 rows=3000000 loops=1)
               Sort Key: a.name DESC
               Sort Method: external merge  Disk: 99864kB
               ->  Seq Scan on movies a  (cost=0.00..68281.00 rows=3000000 width=20) (actual time=0.014..506.638 rows=3000000 loops=1)
 Planning Time: 0.120 ms
 Execution Time: 9683.066 ms
(10 rows)

zjh@postgres=# explain analyze select * from movies order by name desc fetch first 3 rows with ties;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=107055.44..107055.45 rows=3 width=20) (actual time=5583.686..5583.781 rows=300 loops=1)
   ->  Sort  (cost=107055.44..114555.44 rows=3000000 width=20) (actual time=5583.684..5583.732 rows=301 loops=1)
         Sort Key: name DESC
         Sort Method: external merge  Disk: 99864kB
         ->  Seq Scan on movies  (cost=0.00..68281.00 rows=3000000 width=20) (actual time=0.011..253.257 rows=3000000 loops=1)
 Planning Time: 0.095 ms
 Execution Time: 5600.186 ms
(7 rows)