On 2021-03-19 10:29 AM, Thomas Kellerer wrote:
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
...
Thanks, Thomas
I tried that, and it ran about 10% faster. Every little helps, but SQL
Server appears to have some secret sauce!
Frank
