首页 文章

postgresql选择最高值[关闭]

提问于
浏览 次
0

我有以下学生表：

Student_Name Student_RollNo Student_Marks1 Student_Marks2
Andrew       10             90.9           9.2
Blake        2              78.2           100
Fisher       1              69.2           89
Alex         1              98.8           82.1

我的查询：

select * 
from Student
order by (Student_Marks1+Student_Marks2) limit 1;

即我想检索具有最高分的学生的学生详细信息 . 这里最高分是一个函数，它是Student_Marks1和Student_Marks2的总和 . 现在我很想知道postgresql（或mysql）使用哪种算法来执行此排名？

即postgresql首先应用ORDER BY然后LIMIT;或者它首先应用LIMIT然后按ORDER BY;或者它是否使用将ORDER BY和LIMIT一起应用的算法

sql database postgresql postgresql-9.1

2 回答

  • 1

    通常算法很简单：
    1.从 Student 表中取消行
    2.使用ORDER BY表达式从＃1对整个结果集进行排序
    3.应用LIMIT子句（偏移量）以从＃2获得的有序结果集中获取一部分行

    您可以在此处阅读LIMIT的更多信息：http://www.postgresql.org/docs/9.4/static/queries-limit.html

    在某些情况下，在ORDER BY操作（排序）期间会考虑LIMIT以加速查询，尤其是在某些索引可用于消除排序操作时 .

    您可以查看这项工作如何查看解释计划 .
    假设在此表上创建了一个索引：

    create index student_mark1 on student( student_marks1 );

    此查询提供以下解释计划：

    select * From student
order by student_marks2 
limit 1;

Limit  (cost=5.06..5.06 rows=1 width=178) (actual time=0.088..0.089 rows=1 loops=1)
  Output: student_name, student_rollno, student_marks1, student_marks2
  ->  Sort  (cost=5.06..5.57 rows=204 width=178) (actual time=0.088..0.088 rows=1 loops=1)
        Output: student_name, student_rollno, student_marks1, student_marks2
        Sort Key: student.student_marks2
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on public.student  (cost=0.00..4.04 rows=204 width=178) (actual time=0.007..0.021 rows=204 loops=1)
              Output: student_name, student_rollno, student_marks1, student_marks2

    您需要从下到上阅读此计划
    第一个操作是 Seq scan - 这意味着从磁盘读取所有行（整个表 - 请参阅 actual rows = 204 ） .
    然后排序操作被pefrormed（ORDER BY） .
    最后一次操作是LIMIT 1（在计划的顶部）

    将上述计划与此查询进行比较：

    select * From student
order by student_marks1 
limit 1;

Limit  (cost=0.14..0.24 rows=1 width=178) (actual time=0.010..0.010 rows=1 loops=1)
  Output: student_name, student_rollno, student_marks1, student_marks2
  ->  Index Scan using student_mark1 on public.student  (cost=0.14..19.20 rows=204 width=178) (actual time=0.009..0.009 rows=1 loops=1)
        Output: student_name, student_rollno, student_marks1, student_marks2

    这里跳过了排序阶段，因为我们可以使用索引以所需的顺序检索行（ ORDER BY student_marks1 => INDEX ON Student( student_marks1 ) ） .
    请注意最底层操作中的 Actual rows = 1 ：“索引扫描” .
    这意味着，PostgreSQL不扫描整个索引，但只检索索引中的1（第一）行，因为它知道，而不是查询有LIMIT 1子句 . （有时会说PostgreSQL "pushed down"将1个子句限制为索引扫描操作并使用它来减少索引中的扫描条目数） .

    有关使用索引加速ORDER BY的更多信息，请访问：http://www.postgresql.org/docs/8.3/static/indexes-ordering.html

    如果您的问题中的查询， ORDER BY clasue包含表达式 Student_Marks1+Student_Marks2 ，而不是简单的列 . 此查询的解释计划如下所示：

    select *
From student
order by student_marks1 + student_marks2 
limit 2;

Limit  (cost=7.10..7.11 rows=2 width=178) (actual time=0.207..0.207 rows=2 loops=1)
  Output: student_name, student_rollno, student_marks1, student_marks2, (((student_marks1)::numeric + student_marks2))
  ->  Sort  (cost=7.10..7.61 rows=204 width=178) (actual time=0.205..0.205 rows=2 loops=1)
        Output: student_name, student_rollno, student_marks1, student_marks2, (((student_marks1)::numeric + student_marks2))
        Sort Key: (((student.student_marks1)::numeric + student.student_marks2))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on public.student  (cost=0.00..5.06 rows=204 width=178) (actual time=0.019..0.107 rows=204 loops=1)
              Output: student_name, student_rollno, student_marks1, student_marks2, ((student_marks1)::numeric + student_marks2)

    但是你仍然可以通过这种方式加速创建 function based index 的查询：

    create index student_mark12 on student( ( student_marks1 + student_marks2) );

    创建索引后，我们现在有：

    Limit  (cost=0.14..0.34 rows=2 width=178) (actual time=0.044..0.047 rows=2 loops=1)
  Output: student_name, student_rollno, student_marks1, student_marks2, (((student_marks1)::numeric + student_marks2))
  ->  Index Scan using student_mark12 on public.student  (cost=0.14..20.22 rows=204 width=178) (actual time=0.043..0.046 rows=2 loops=1)
        Output: student_name, student_rollno, student_marks1, student_marks2, ((student_marks1)::numeric + student_marks2)

    请注意，Postgre在这种情况下使用索引，并根据 LIMIT 2 子句从中检索仅2个条目（实际行= 2） .

    回复于
  • 1

    PostgreSQL使用默认的B树操作符类来确定ORDER BY表达式中的排序顺序（ASC作为默认值，使用<首先排序较小，DESC使用>来变大） .

    至于有关多个属性的注释问题，它会根据您对排序字段的输入顺序使用ORDER BY .

    回复于

相关问题