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
anythingobvious 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