Frank Millman schrieb am 19.03.2021 um 09:52: >>> I am writing a cross-platform accounting app, and I test using Sql >>> Server on Windows 10 and PostgreSql on Fedora 31. Performance is >>> usually very similar, with a slight edge to PostgreSql. Now I have a >>> SELECT which runs over twice as fast on Sql Server compared to >>> PostgreSql. >>> >> Can you change the SELECT statement? >> >> Very often "distinct on ()" is faster in Postgres compared to the equivalent solution using window functions > > Thanks, Thomas > > I tried that, and it ran about 10% faster. Every little helps, but SQL Server appears to have some secret sauce! The two derived tables (cl_bal, op_bal) seem to be doing exactly the same thing - at least I can't spot a difference. If that is correct, you can move them into a common table expression - maybe detecting that is SQL Server's secret sauce. with totals as ( SELECT a.source_code_id, SUM(a.tran_tot) AS total FROM ( SELECT distinct on (location_row_id, function_row_id, source_code_id) source_code_id, tran_tot FROM prop.ar_totals WHERE deleted_id = 0 AND tran_date <= '2018-03-31' AND ledger_row_id = 1 ORDER BY location_row_id, function_row_id, source_code_id, tran_date DESC ) AS a GROUP BY a.source_code_id ) select '2018-03-01' AS op_date, '2018-03-31' AS cl_date, cl_bal.source_code_id, op_bal.total as op_tot, cl_bal.total.cl_tot FROM totals as cl_bal LEFT JOIN totals as op_bal ON op_bal.source_code_id = cl_bal.source_code_id;