pá 19. 3. 2021 v 9:53 odesílatel Frank Millman <fr...@chagford.com> napsal:
> > 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! > can you send a result of EXPLAIN ANALYZE? Pavel > Frank > > > > >
