Re: SELECT is faster on SQL Server

On 2021-03-19 5:32 PM, Jehan-Guillaume de Rorthais wrote:
> On Fri, 19 Mar 2021 14:28:27 +0200
> Frank Millman <frank(at)chagford(dot)com> wrote:
>
>> [...]
>> Execution of my main query has improved from 50ms to 33ms. Sql Server
>> takes 25ms, but this is much better than it was.
>>
>> [...]
>>
>> Here is the new EXPLAIN ANALYSE -
>>
>> QUERY PLAN
>> --------------------------------------------------------------------
>>  Merge Left Join  (...) (actual time=1.566..1.581 rows=5 loops=1)
> 1.581ms to output the very last row of this plan. This is in contradiction with
> the 33ms you are referencing above.
>
> What do I miss here? Maybe your 33ms comes yet from another set of data? Could
> you share an explain analyze actually showing this 33ms total execution time?

Sorry, I should have explained.

The query I showed selects data for a single month. The 'real' query
repeats this 12 times, each with different dates, and combines the
results using UNION ALL. This was the timing mentioned above.

BTW, I know that I can improve this by setting up the dates in a CTE and
using JOIN LATERAL. I am avoiding this as it is not supported by SQL
Server or sqlite3, and I am trying to stick to one code base for all
databases. But I will look into it further.

Frank