Re: SELECT is faster on SQL Server

Frank Millman schrieb am 19.03.2021 um 09:19:
> This may be a non-issue, and I don't want to waste your time. But perhaps someone can have a look to see if there is anything obvious I have missed.
>
> 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

Something along the lines (for the first derived table):

SELECT ...
FROM (
SELECT a.source_code_id, SUM(a.tran_tot) AS cl_tot
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
) as cl_bal
...

Thomas