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)