Re: SELECT is faster on SQL Server

On 2021-03-19 12:00 PM, Pavel Stehule wrote:
>
> In this query the most slow operation is query planning. You try to do
> tests on almost empty tables. This has no practical sense. You should
> test queries on tables with size similar to production size.
>
Sorry about that. I hope this one is better. Same query, different data set.

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=1401.00..1401.12 rows=1 width=132) (actual
time=3.595..3.611 rows=5 loops=1)
   Merge Cond: (a.source_code_id = a_1.source_code_id)
   ->  GroupAggregate  (cost=673.16..673.18 rows=1 width=36) (actual
time=1.101..1.108 rows=5 loops=1)
         Group Key: a.source_code_id
         ->  Sort  (cost=673.16..673.16 rows=1 width=12) (actual
time=1.092..1.093 rows=5 loops=1)
               Sort Key: a.source_code_id
               Sort Method: quicksort  Memory: 25kB
               ->  Subquery Scan on a  (cost=670.67..673.15 rows=1
width=12) (actual time=1.008..1.086 rows=5 loops=1)
                     Filter: (a.row_num = 1)
                     Rows Removed by Filter: 59
                     ->  WindowAgg  (cost=670.67..672.37 rows=62
width=36) (actual time=1.006..1.076 rows=64 loops=1)
                           ->  Sort  (cost=670.67..670.82 rows=62
width=28) (actual time=0.996..1.004 rows=64 loops=1)
                                 Sort Key: ar_totals.location_row_id,
ar_totals.function_row_id, ar_totals.source_code_id, ar_totals.tran_date
DESC
                                 Sort Method: quicksort  Memory: 30kB
                                 ->  Seq Scan on ar_totals
(cost=0.00..668.82 rows=62 width=28) (actual time=0.012..0.933 rows=64
loops=1)
                                       Filter: ((tran_date <=
'2015-04-30'::date) AND (deleted_id = 0) AND (ledger_row_id = 1))
                                       Rows Removed by Filter: 840
   ->  GroupAggregate  (cost=727.85..727.89 rows=2 width=36) (actual
time=2.490..2.495 rows=5 loops=1)
         Group Key: a_1.source_code_id
         ->  Sort  (cost=727.85..727.85 rows=3 width=12) (actual
time=2.485..2.485 rows=5 loops=1)
               Sort Key: a_1.source_code_id
               Sort Method: quicksort  Memory: 25kB
               ->  Subquery Scan on a_1  (cost=700.70..727.82 rows=3
width=12) (actual time=1.684..2.479 rows=5 loops=1)
                     Filter: (a_1.row_num = 1)
                     Rows Removed by Filter: 674
                     ->  WindowAgg  (cost=700.70..719.35 rows=678
width=36) (actual time=1.682..2.397 rows=679 loops=1)
                           ->  Sort  (cost=700.70..702.40 rows=678
width=28) (actual time=1.676..1.758 rows=679 loops=1)
                                 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
                                 Sort Method: quicksort  Memory: 78kB
                                 ->  Seq Scan on ar_totals ar_totals_1 
(cost=0.00..668.82 rows=678 width=28) (actual time=0.007..0.836 rows=679
loops=1)
                                       Filter: ((tran_date <
'2015-09-01'::date) AND (deleted_id = 0) AND (ledger_row_id = 1))
                                       Rows Removed by Filter: 225
 Planning Time: 0.496 ms
 Execution Time: 3.695 ms
(34 rows)