sql - Optimized query with performance measurement (from mysql to) in postgresql -

select m.title , m.run_time movie m m.run_time < (select avg (run_time) movie) *1.1 , m.run_time > (select avg (run_time) movie) *0.9; 

it costs 4.6 8 in postgresql

baisically selects title&runtime of movies within 10% of average runtime. movie table goes this:

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) ); 

and has 101 entries.

since "select avg (run_time) movie" used twice, thought of sticking average in variable, , referring variable in 2nd query. mysql looks this, runs, , sum of 2 commands times shorter reference query above.

set @average = (select avg (run_time) movie); select m.title , m.run_time movie m m.run_time < @average *1.1 , m.run_time > @average *0.9; 

now, how equivalently in postgresql? have listed attempts below

when try make variable in postgresql, so:

\set average (select avg (run_time) movie); 

this works. next line:

select m.title , m.run_time movie m m.run_time < :average *1.1 , m.run_time > :average *0.9;  error:  syntax error @ or near "frommovie" line 1: ...om movie m m.run_time < (selectavg(run_time)frommovie)... 

happens, think because \set places command literally, string variable, , doesn''t evaluate it, unlike mysql.

so try make temporary table

create temp table temptable ( theaverage float ); insert temptable  ( select avg (m.run_time) movie m ); select m.title , m.run_time movie m m.run_time < (select * temptable) *1.1 , m.run_time > (select * temptable) *0.9; 

these work. (measuring) performance is, not good.

explain analyze create temp table temptable ( theaverage float ); //cannot analyze this/does not work/syntax error happens. error:  syntax error @ or near "float" line 1: ...in analyze create temp table temptable ( theaverage float );  explain insert temptable  ( select avg (m.run_time) movie m ); //costs 2.3ish  explain select m.title , m.run_time movie m m.run_time < (select theaverage temptable) *1.1 , m.run_time > (select theaverage temptable) *0.9; 

//costs 63 66, wat? make cost signifigantly more unoptimized query, 4.6 8.

i have tried select into, couldn't figure out how use correctly purposes.

so, i'll repeat question, how make optimized version of

"select m.title , m.run_time movie m m.run_time < (select avg (run_time) movie) *1.1 , m.run_time > (select avg (run_time) movie) *0.9;" 

perhaps using variables, , performance measurement, in postgresql?

this solution not use variables , works both postgresql , mysql:

select m.title, m.run_time movie m,      (select avg(run_time) time movie) m.run_time between a.time * 0.9                      , a.time * 1.1 

obligatory sqlfiddle.

note adding index on column run_time should improve performance of query (provided movies table large):

create index movies_run_time_idx on movies(run_time);