mysql - 在 postgresql 中使用性能测量(从 mysql 到)优化查询

Question

SELECT m.title , m.run_time FROM movie m WHERE m.run_time < (SELECT AVG (run_time) FROM movie) *1.1 AND m.run_time > (SELECT AVG (run_time) FROM movie) *0.9;

在postgresql中花费4.6到8

基本上，它会选择平均运行时间在 10% 以内的电影的标题和运行时间。电影表是这样的:

CREATE TABLE MOVIE
(
title             varchar(40)                             NOT NULL,
production_year        smallint                             NOT NULL,
country         varchar(20)                            NOT NULL,
run_time        smallint                            NOT NULL,
major_genre         varchar(15)                                 ,
CONSTRAINT pk_movie PRIMARY KEY(title,production_year)
);

并且有 101 个条目。

由于“SELECT AVG (run_time) FROM movie”被使用了两次，我想到将平均值放在一个变量中，并在第二次查询中引用该变量。Mysql是这样的，运行起来，两条命令的总时间比上面的引用查询要短。

SET @average = (SELECT AVG (run_time) FROM movie);
SELECT m.title , m.run_time FROM movie m WHERE m.run_time < @average *1.1 AND m.run_time > @average *0.9;

现在，如何在 postgresql 中等效地执行此操作？我在下面列出了我的尝试

当我尝试在 postgresql 中创建变量时，如下所示:

\set average (SELECT AVG (run_time) FROM movie);

这行得通。但下一行:

SELECT m.title , m.run_time FROM movie m WHERE m.run_time < :average *1.1 AND m.run_time > :average *0.9;

ERROR:  syntax error at or near "FROMmovie"
LINE 1: ...OM movie m WHERE m.run_time < (SELECTAVG(run_time)FROMmovie)...

发生了，我认为是因为\set 按字面意思放置了我的命令，就像字符串变量一样，并且不对其求值，这与 mysql 不同。

所以我尝试做一个临时表

CREATE TEMP TABLE temptable ( theaverage float );
insert into temptable  ( SELECT AVG (m.run_time) FROM movie m );
SELECT m.title , m.run_time FROM movie m WHERE m.run_time < (Select * from temptable) *1.1 AND m.run_time > (Select * from temptable) *0.9;

这些工作。但是(测量)性能不是很好。

explain analyze CREATE TEMP TABLE temptable ( theaverage float ); //cannot analyze this/does not work/syntax error happens.
ERROR:  syntax error at or near "float"
LINE 1: ...in analyze CREATE TEMP TABLE temptable ( theaverage float );

explain insert into temptable  ( SELECT AVG (m.run_time) FROM movie m ); //costs 2.3ish

explain SELECT m.title , m.run_time FROM movie m WHERE m.run_time < (Select theaverage from temptable) *1.1 AND m.run_time > (Select theaverage from temptable) *0.9;

//费用从 63 到 66，哇？这将使它的成本显着高于未优化的查询，即 4.6 到 8。

我也试过 SELECT INTO，但我不知道如何正确使用它来达到我的目的。

那么，我再重复一遍这个问题，如何制作优化版的

"SELECT m.title , m.run_time FROM movie m WHERE m.run_time < (SELECT AVG (run_time) FROM movie) *1.1 AND m.run_time > (SELECT AVG (run_time) FROM movie) *0.9;"

也许通过在 postgresql 中使用变量和性能测量？

Answer 1

此解决方案不使用变量并且适用于 PostgreSQL 和 MySQL:

SELECT m.title, m.run_time
FROM movie m, 
    (SELECT avg(run_time) AS time FROM movie) a
WHERE m.run_time BETWEEN a.time * 0.9
                     AND a.time * 1.1

强制性 SQLFiddle .

请注意，在 run_time 列上添加索引应该会提高此查询的性能(前提是您的 movies 表很大):

CREATE INDEX movies_run_time_idx ON movies(run_time);