我得到了包含数据的下一个表:
CREATE TABLE xml_files (
"parsing_status" Character Varying( 150 ),
"purchaseNumber" Character Varying( 2044 ),
"docPublishDate" Timestamp With Time Zone );
;数据:
purchaseNumber parsing_status docPublishDate
0373200554017000226 null 2017-07-28 19:00:10.885+03
0373200554017000226 null 2017-07-28 19:08:30.346+03
0373200554017000226 null 2017-07-28 19:24:35.265+03
0373400005317002182 null 2017-07-28 19:45:02.162+03
0348100035117000082 null 2017-07-28 20:08:26.37+03
0373200554017000292 null 2017-07-28 20:10:24.312+03
0373200081217000531 null 2017-07-28 20:13:56.166+03
0373200041517000400 null 2017-07-28 21:23:20.616+03
0373200081217000531 null 2017-07-29 08:18:29.571+03
0373200081217000531 null 2017-07-29 09:34:11.545+03
0373100026117000078 null 2017-07-29 10:37:01.161+03
0573400000117001086 null 2017-07-29 11:25:37.863+03
0573400000117001096 null 2017-07-29 11:30:36.499+03
0373200081217000531 null 2017-07-29 12:14:04.033+03
0573400000117001118 null 2017-07-29 14:50:34+03
0573400000117001118 null 2017-07-29 16:49:12.457+03
0373100026117000080 null 2017-07-29 16:52:02.013+03
0373100026117000080 null 2017-07-29 17:05:40.981+03
0373100026117000080 null 2017-07-29 17:13:29.532+03
0373200554017000226 null 2017-07-29 18:55:47.488+03列purchaseNumber有重复项。我需要选择处理所有最新的未解析记录。我正在使用next SQL:
SELECT
"purchaseNumber", "parsing_status", "docPublishDate"
FROM (
select distinct on ("purchaseNumber") x.*
from xml_files x
order by "purchaseNumber", "docPublishDate" desc
) x
where parsing_status is distinct from 'true'
AND parsing_status IS NULL
order by "docPublishDate" LIMIT 100问题是,查询在具有数百万行表格上花费了很长时间。如何提高速度?以下是数据示例:https://www.db-fiddle.com/f/vycMHGLYML5K56SN77HLsY/0
发布于 2020-12-29 21:37:48
对于您的查询,您需要xml_files("purchaseNumber", "docPublishDate" desc)上的索引
create index idx_xml_files_2 on xml_files("purchaseNumber", "docPublishDate" desc)Postgres应该对order by使用这个索引,这有助于distinct on。
虽然它不会影响性能,但我也建议将where子句简化为:
where parsing_status IS NULL 发布于 2020-12-29 23:26:50
好的,我会给你一些建议
1.改进查询
SELECT t1.purchaseNumber, t1.parsing_status, t1.docPublishDate
FROM xml_files t1
LEFT JOIN xml_files t2
ON t1.purchaseNumber = t2.purchaseNumber
AND t1.docPublishDate < t2.docPublishDate
WHERE t1.parsing_status IS NULL
AND t2.parsing_status IS NULL
AND t2.docPublishDate IS NULL2.改进表格
您也可以尝试添加一些索引,但如果表只包含这3列,我不确定您可以提高多少。根据数据分布情况,例如,如果您知道parsing_status的一半为空,则可以尝试:
create index idx_xml_files_2 on xml_files("parsing_status", "purchaseNumber")https://stackoverflow.com/questions/65493027
复制相似问题