sql - PostgreSQL:按字母数字字符串和小数字段排序

Question

希望你们一切都好!

我有一个包含 200 万条记录的表items，结构如下所示:

id (int) | price (decimal) | priority (int)
-------------------------------------------
10001    |  59000.25       |    1
10002    |  73000.91       |    2
10003    |  1000.23        |    1
10004    |  9567.18        |    1

我正在寻找的解决方案非常简单:如何按 ASC|DESC 顺序按 price+priority 对表进行排序？

当前有效的解决方案:我正在使用ORDER BY priority ASC, price ASC。但据我所知，对多列进行排序很慢，而且不是经过优化的方法(因此我面临着实时缓慢的问题)。

我尝试过的解决方案:我在这个表中添加了一个临时列:

id (int) | price (decimal) | priority (int) | new_priority (varchar)
--------------------------------------------------------------------
10001    |  59000.25       |    1           | a59000.25
10002    |  73000.91       |    2           | b73000.91
10003    |  1000.23        |    1           | a1000.23
10004    |  9567.18        |    1           | a9567.18

我已经替换了 1 => a, 2 => b, 3 => c 直到 10(我在数据库中的最大数量)

现在，每当我尝试使用以下 SQL 时，它们都不起作用

SELECT * FROM items
ORDER BY new_priority ASC

SELECT * FROM items
ORDER BY new_priority::bytea

SELECT * FROM items
ORDER BY SUBSTRING(new_priority FROM '^(.*?)( \\d+)?$'),
     COALESCE(SUBSTRING(new_priority FROM ' (\\d+)$')::INTEGER, 0)

请指教!

我引用的链接:

1. Postgresql sorting mixed alphanumeric data
2. Alphanumeric Sorting in PostgreSQL
3. Alphanumeric sorting with PostgreSQL
4. Alphanumeric case in-sensitive sorting in postgres

Answer 1

如何创建一个 index ？索引是一种提高数据库性能的机制。创建索引可能会很慢并且可能会持续几个小时，但您会注意到进一步查询中的差异。