From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: SELECT is faster on SQL Server |
Date: | 2021-03-19 17:11:45 |
Message-ID: | 47da8273-23dd-789a-ca7d-a5b803d9010f@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Frank Millman schrieb am 19.03.2021 um 10:16:
>>>> Very often "distinct on ()" is faster in Postgres compared to the equivalent solution using window functions
>> The two derived tables (cl_bal, op_bal) seem to be doing exactly the same thing - at least I can't spot a difference.
>>
>> If that is correct, you can move them into a common table expression - maybe detecting that is SQL Server's secret sauce.
>>
> There is a difference.
>
> cl_bal selects WHERE tran_date <= '2018-03-31'.
>
> op_bal selects WHERE tran_date < '2018-03-01'.
>
> The second one could be written as WHERE tran_date <= '2018-02-28', but I don't think that would make any difference.
I knew I overlooked something ;)
But as one is a true subset of the other, I think you can merge that into a single SELECT statement:
select '2018-03-01' AS op_date,
'2018-03-31' AS cl_date,
a.source_code_id,
sum(a.tran_tot) AS cl_tot,
sum(a.tran_tot) filter (where tran_date < '2018-03-01') AS op_tot
FROM (
SELECT distinct on (location_row_id, function_row_id, source_code_id) source_code_id, tran_tot, tran_date
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
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Millman | 2021-03-20 06:13:50 | Re: SELECT is faster on SQL Server |
Previous Message | Laurenz Albe | 2021-03-19 15:58:55 | Re: postgresql order lowercase before uppercase |