主页 / dba / 问题 / 160237
Accepted
joanolo
Asked: 2017-01-07 15:32:53 +0800 CST

让 CASE .. END 在 ORDER BY 中有意义吗？

  • 772

像这样的查询SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end是可能的，但是：这是一个好习惯吗？

通常在查询的 WHERE 部分使用参数，并在 SELECT 部分有一些计算列，但参数化 ORDER BY 子句并不常见。

假设我们有一个列出二手车的应用程序（à la CraigsList）。汽车列表可以按价格或颜色排序。我们有一个函数，给定一定数量的参数（例如价格范围、颜色和排序标准）返回一组记录和结果。

为了使其具体化，我们假设cars都在下表中：

CREATE TABLE cars
(
  car_id serial NOT NULL PRIMARY KEY,  /* arbitrary anonymous key */
  make text NOT NULL,       /* unnormalized, for the sake of simplicity */
  model text NOT NULL,      /* unnormalized, for the sake of simplicity */
  year integer,             /* may be null, meaning unknown */
  euro_price numeric(12,2), /* may be null, meaning seller did not disclose */
  colour text               /* may be null, meaning unknown */
) ;

该表将具有大多数列的索引...

CREATE INDEX cars_colour_idx
  ON cars (colour);
CREATE INDEX cars_price_idx
  ON cars (price);
/* etc. */

并且有一些商品枚举：

CREATE TYPE car_sorting_criteria AS ENUM
   ('price',
    'colour');

...和一些样本数据

INSERT INTO cars.cars (make, model, year, euro_price, colour)

VALUES 
    ('Ford',   'Mondeo',   1990,  2000.00, 'green'),
    ('Audi',   'A3',       2005,  2500.00, 'golden magenta'),
    ('Seat',   'Ibiza',    2012, 12500.00, 'dark blue'),
    ('Fiat',   'Punto',    2014,     NULL, 'yellow'),
    ('Fiat',   '500',      2010,  7500.00, 'blueish'),
    ('Toyota', 'Avensis',  NULL,  9500.00, 'brown'), 
    ('Lexus',  'CT200h',   2012, 12500.00, 'dark whitish'), 
    ('Lexus',  'NX300h',   2013, 22500.00, NULL) ;

我们要进行的查询类型如下：

SELECT
    make, model, year, euro_price, colour
FROM
    cars.cars
WHERE
    euro_price between 7500 and 9500 
ORDER BY
    colour ;

我们想在函数中查询这种风格：

CREATE or REPLACE FUNCTION get_car_list
   (IN _colour    text, 
    IN _min_price numeric, 
    IN _max_price numeric, 
    IN _sorting_criterium car_sorting_criteria) 
RETURNS record AS
$BODY$
      SELECT
          make, model, year, euro_price, colour
      FROM
          cars
      WHERE
           euro_price between _min_price and _max_price
           AND colour = _colour
      ORDER BY
          CASE WHEN _sorting_criterium = 'colour' THEN
            colour
          END,
          CASE WHEN _sorting_criterium = 'price' THEN
            euro_price
          END 
$BODY$
LANGUAGE SQL ;

代替这种方法，这个函数中的 SQL 可以作为字符串动态生成（在 PL/pgSQL 中），然后执行。

我们可以感觉到任何一种方法的一些限制、优点和缺点：

  1. 在一个函数中，很难找到某个语句的查询计划（如果可能的话）。然而，当我们经常使用某些东西时，我们倾向于使用函数。
  2. 静态 SQL 中的错误将（大部分）在编译函数或首次调用时被捕获。
  3. 动态 SQL 中的错误只会在函数编译后才被捕获（moslty），并且所有执行路径都已检查（即：对函数执行的测试次数可能非常多）。
  4. 像公开的那样的参数查询可能比动态生成的查询效率低；然而，执行者将有更难的工作解析/制作查询树/每次决定（这可能会影响相反方向的效率）。

问题：

如何“两全其美”（如果可能）？【效率+编译检查+轻松调试+轻松优化】

注意：这将在 PostgreSQL 9.6 上运行。

postgresql functions

4 个回答

  1. 我要提出的三点，

    1. 这是一个非常基本的查询，即使在您应用的版本中也是如此。为它创建一个VIEW。让您的用户WHERE使用VIEW. 函数是查询计划器的黑盒。在其他函数中使用它们很可怕，只有SQL内联。而且，动态函数不会获得缓存计划。
    2. 如果要继续使用 plpgsql，请使用RETURNS QUERY（或RETURNS QUERY EXECUTE）不RETURNS SETOF。没有理由使用RETURNS SETOF排序。无论如何，它必须被缓冲，afaik。如果您的结果集大于work_mem.
    3. 我不确定你的应用程序是用什么写的。我假设 web. 不过，我在汽车行业工作了十年，做了很多东西，比如Craigslist 和 Craigslist 的发布工具。“不要为数据库中的用户排序”通常是一个很好的经验法则。没有理由这样做。把这些东西放在 JSON 中，让他们在浏览器中处理。除非您显示超过 1000 行，否则它甚至不值得考虑。只需考虑手机信号塔的往返时间。你永远不会有时间回来思考这个问题。

    展望未来，我什至会考虑包装一个像PostgREST这样的服务来处理完全任意的排序，

    If you care where nulls are sorted, add nullsfirst or nullslast:

GET /people?order=age.nullsfirst
GET /people?order=age.desc.nullslast
    • 3
  2. Best Answer

    一般回答

    首先，我想解决前提中的歧义：

    通常在查询的 WHERE 部分使用参数，并在 SELECT 部分有一些计算列，但参数化 ORDER BY 子句并不常见。

    该部分中的计算列SELECT几乎与查询计划或性能无关。但“在WHERE部分”是模棱两可的。

    在子句中参数化值是很常见的WHERE，这适用于准备好的语句。（并且 PL/pgSQL 在内部使用准备好的语句。）无论提供的值如何，通用查询计划通常都是有意义的。也就是说，除非表的数据分布非常不均匀，但由于 Postgres 9.2 PL/pgSQL 重新计划查询几次以测试通用计划是否足够好：

    但是在子句中参数化整个谓词（包括标识符）并不常见WHERE，这对于一开始就准备好的语句是不可能的。您需要带有 的动态 SQL EXECUTE，或者您在客户端中组装查询字符串。

    动态ORDER BY表达式介于两者之间。您可以使用表达式来做到这一点CASE，但这通常很难优化。Postgres 可以使用带有 plain 的索引ORDER BY，但不能使用CASE隐藏最终排序顺序的表达式。计划者很聪明，但不是人工智能。根据查询的其余部分（ORDER BY可能与计划相关或不相关 - 在您的示例中相关），您可能最终得到一个次优的查询计划。
    另外，您添加了CASE表达式的次要成本。在您的特定示例中，也适用于多个无用的ORDER BY列。

    通常，动态 SQLEXECUTE会更快或更快。

    如果您在函数体中保持清晰易读的代码格式，可维护性应该不是问题。

    修复演示功能

    问题中的功能已损坏。返回类型定义为返回匿名记录：

    RETURNS record AS

    但是查询实际上返回了一组记录，它必须是：

    RETURNS SETOF record AS

    但这仍然无济于事。您必须在每次调用时提供一个列定义列表。您的查询返回已知类型的列。相应地声明返回类型！我在这里猜测，使用返回的列/表达式的实际数据类型：

    RETURNS TABLE (make text, model text, year int, euro_price int, colour text) AS

    为方便起见，我使用相同的列名。RETURNS TABLE子句中的列实际上是OUT参数，在主体中的每个 SQL 语句中可见（但在内部不可见EXECUTE）。因此，在函数体的查询中对列进行表限定以避免可能的命名冲突。演示功能将像这样工作：

    CREATE or REPLACE FUNCTION get_car_list (
    _colour            text, 
    _min_price         numeric, 
    _max_price         numeric, 
    _sorting_criterium car_sorting_criteria) 
  RETURNS TABLE (make text, model text, year int, euro_price numeric, colour text) AS
$func$
      SELECT c.make, c.model, c.year, c.euro_price, c.colour
      FROM   cars c
      WHERE  c.euro_price BETWEEN _min_price AND _max_price
      AND    c.colour = _colour
      ORDER  BY CASE WHEN _sorting_criterium = 'colour' THEN c.colour     END
              , CASE WHEN _sorting_criterium = 'price'  THEN c.euro_price END;
$func$  LANGUAGE sql;

    不要像Evan 在他的回答中所做的那样RETURNS，将函数声明中的关键字与 plpgsqlRETURN命令混淆。细节：

    示例查询的一般难度

    某些列上的谓词（更糟糕的是：范围谓词）， 中的其他列ORDER BY，这已经很难优化了。但你在评论中提到：

    实际结果集可能是几 1.000 行的顺序（因此，在服务器端以较小的块进行排序和分页）

    因此，您将向这些查询添加LIMIT和OFFSET，首先返回n 个“最佳”匹配项。或者一些更智能的分页技术：

    您需要一个匹配的索引来加快速度。我不明白这怎么可能CASE与ORDER BY.

    考虑：

    • 3

  3. 在所描述的案例中按函数的顺序排列案例是没有意义的

    索引不会以这种方式在 order by 子句中使用。数据库引擎必须计算每一行的 case 表达式然后排序。而且这种技术不能扩展到动态连接和过滤器。

    我会使用动态 SQL 生成...

    在 PostgreSQL 函数中生成动态 SQL

    你可以这样做：

      create or replace function fn_test(car_sort_option) returns text as $$
  declare
      xsql text;
      xresult text;
  begin

    xsql = 'select array_to_json(array_agg(c)) from cars';

    if $1 = 'colour' then 
      xsql = xsql || ' order by colour';
    elsif $1 = 'model' then  
      xsql = xsql || ' order by model';
    else 
      raise exception 'invalid parameter: sort option=% is invalid', $1; 
    end if;

    execute xsql into xresult;

    return xresult;
  end

  $$ language 'plpgsql';

    我通常不会为 PL/pgSQL 中的表示层如此特别的东西创建动态 SQL。我通常更喜欢将这个 SQL 构建留在 PHP 或 Java 中。但是对于很多其他的事情，我在 PL/pgSQL 中做了很多动态 SQL。主要用于分区、数据库维护、工作流实现和数据一致性控制。

    我发现这个策略让代码更干净，更好地使用索引，并且可以在更复杂的动态 SQL 中使用。索引的使用在我的世界中至关重要，因为我在具有数十亿条记录的数据库中进行分析并且需要快速响应。

    关于为什么在数据库之外生成动态 SQL 更为常见的附加说明（以下评论）

    发生这种情况是因为大多数动态 SQL 仅在表名或列名是动态的时才需要。对于其他所有参数化查询都可以。大多数报告都需要动态 SQL。不仅因为您需要选择排序顺序列，而且大多数时候您需要动态包含过滤器和列。许多开发人员在表示层的报表程序中或在生成它们的批处理中不断地报告 SQL，在这种情况下，动态 SQL 也会在数据库之外生成。

    • 1

  4. 在这种情况下，我会使用动态 SQL。
    动态查询不太复杂，可能会产生更好的执行计划。
    根据我的经验，计划缓存或更短的解析时间带来的任何好处都可以忽略不计。

    我曾经遵循“仅当静态 SQL 不起作用时才使用动态 SQL”的规则。今天作为一个经验法则，我通常根据我需要灵活的 SQL 查询的子句来选择它：

    • WHERE - 静态 SQL
    • FROM、ORDER BY、GROUP BY - 动态 SQL
    • 选择 - 备用

    诚然，调试起来有点困难，但使用一些好的实践应该有助于缩小差距。
    例如，在 plpgsql 中，您可以使用 $ 表示法 + REPLACE + RAISE NOTICE： 

    -- Write the entire query using $ for replacements.
-- Don't use || operator.
-- This makes the dynamic query clearer to read and easier to maintain and debug.
v_sql:= 'SELECT <some columns> FROM tbl ORDER BY $sorting$';

v_sql:= REPLACE(v_sql,
  '$sorting$',
  CASE condition
    WHEN value1 THEN 'colX'
    WHEN value2 THEN 'colY'
  END);

-- Debug the query when running
RAISE NOTICE '%', v_sql;
RETURN QUERY EXECUTE v_sql;

    您应该能够在第一次运行时捕获任何语法错误。
    逻辑错误与静态 SQL 一样容易/难以找到。

    • 1

相关问题