SELECT is faster on SQL Server

[Frank Millman <frank@xxxxxxxxxxxx>]

Hi all

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.

This is the table definition -

                                         Table "prop.ar_totals"
     Column      |     Type      | Collation | Nullable 
|                    Default
-----------------+---------------+-----------+----------+------------------------------------------------
 row_id          | integer       |           | not null | 
nextval('prop.ar_totals_row_id_seq'::regclass)
 created_id      | integer       |           |          | 0
 deleted_id      | integer       |           |          | 0
 ledger_row_id   | integer       |           |          |
 location_row_id | integer       |           |          |
 function_row_id | integer       |           |          |
 source_code_id  | integer       |           |          |
 tran_date       | date          |           |          |
 tran_day        | numeric(21,2) |           |          | 0
 tran_tot        | numeric(21,2) |           |          | 0
Indexes:
    "ar_totals_pkey" PRIMARY KEY, btree (row_id)
    "_ar_totals" UNIQUE, btree (ledger_row_id NULLS FIRST, 
location_row_id NULLS FIRST, function_row_id NULLS FIRST, source_code_id 
NULLS FIRST, tran_date NULLS FIRST) WHERE deleted_id = 0
    "ar_tots_cover" btree (ledger_row_id NULLS FIRST, location_row_id 
NULLS FIRST, function_row_id NULLS FIRST, source_code_id NULLS FIRST, 
tran_date DESC NULLS LAST, tran_day NULLS FIRST, tran_tot NULLS FIRST) 
WHERE deleted_id = 0

This is the SELECT -

SELECT
    '2018-03-01' AS op_date, '2018-03-31' AS cl_date,
    cl_bal.source_code_id, op_bal.op_tot, cl_bal.cl_tot
FROM (
    SELECT a.source_code_id, SUM(a.tran_tot) AS cl_tot FROM (
        SELECT source_code_id, tran_tot,
        ROW_NUMBER() OVER (PARTITION BY
        ledger_row_id, location_row_id, function_row_id, source_code_id
        ORDER BY tran_date DESC) row_num
        FROM prop.ar_totals WHERE deleted_id = 0 AND tran_date <= 
'2018-03-31'
            AND ledger_row_id = 1
        ) AS a
    WHERE a.row_num = 1
    GROUP BY a.source_code_id
    ) as cl_bal
LEFT JOIN (
    SELECT a.source_code_id, SUM(a.tran_tot) AS op_tot FROM (
        SELECT source_code_id, tran_tot,
        ROW_NUMBER() OVER (PARTITION BY
        ledger_row_id, location_row_id, function_row_id, source_code_id
        ORDER BY tran_date DESC) row_num
        FROM prop.ar_totals WHERE deleted_id = 0 AND tran_date < 
'2018-03-01'
            AND ledger_row_id = 1
        ) AS a
    WHERE a.row_num = 1
    GROUP BY a.source_code_id
    ) as op_bal
ON op_bal.source_code_id = cl_bal.source_code_id

This is the EXPLAIN -

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=5.66..5.74 rows=1 width=132)
   Join Filter: (a_1.source_code_id = a.source_code_id)
   ->  GroupAggregate  (cost=3.65..3.67 rows=1 width=36)
         Group Key: a.source_code_id
         ->  Sort  (cost=3.65..3.65 rows=1 width=10)
               Sort Key: a.source_code_id
               ->  Subquery Scan on a  (cost=2.36..3.64 rows=1 width=10)
                     Filter: (a.row_num = 1)
                     ->  WindowAgg  (cost=2.36..3.24 rows=32 width=34)
                           ->  Sort  (cost=2.36..2.44 rows=32 width=26)
                                 Sort Key: ar_totals.location_row_id, 
ar_totals.function_row_id, ar_totals.source_code_id, ar_totals.tran_date 
DESC
                                 ->  Seq Scan on ar_totals 
(cost=0.00..1.56 rows=32 width=26)
                                       Filter: ((tran_date <= 
'2018-03-31'::date) AND (deleted_id = 0) AND (ledger_row_id = 1))
   ->  GroupAggregate  (cost=2.01..2.03 rows=1 width=36)
         Group Key: a_1.source_code_id
         ->  Sort  (cost=2.01..2.02 rows=1 width=10)
               Sort Key: a_1.source_code_id
               ->  Subquery Scan on a_1  (cost=1.68..2.00 rows=1 width=10)
                     Filter: (a_1.row_num = 1)
                     ->  WindowAgg  (cost=1.68..1.90 rows=8 width=34)
                           ->  Sort  (cost=1.68..1.70 rows=8 width=26)
                                 Sort Key: ar_totals_1.location_row_id, 
ar_totals_1.function_row_id, ar_totals_1.source_code_id, 
ar_totals_1.tran_date DESC
                                 ->  Seq Scan on ar_totals ar_totals_1  
(cost=0.00..1.56 rows=8 width=26)
                                       Filter: ((tran_date < 
'2018-03-01'::date) AND (deleted_id = 0) AND (ledger_row_id = 1))
(24 rows)

Maybe SQL Server has a way of optimising this, and there is nothing more 
I can do. I can live with that. But I just thought I would ask the question.

Thanks for any advice.

Frank Millman