Re: SELECT is faster on SQL Server

On 2021-03-19 12:58 PM, Frank Millman wrote:
> 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)
>

@Pavel & depesz

Thanks for the replies. I am now focusing on the index. I tried dropping
the index 'ar_tots_cover', and then adding back the index columns one at
a time. Adding 'tran_date desc' made a small difference. Adding
'tran_day' and 'tran_tot' made a big difference. This changed the index
into a 'covering' index, and this is reflected in the new EXPLAIN
ANALYSE (see below).

Execution of my main query has improved from 50ms to 33ms. Sql Server
takes 25ms, but this is much better than it was.

However, the bizarre thing is that I have simply restored the index to
what it was in the first place. If you look at the table definition in
my original message you can see that all the columns were included in
the index. But the query did not use it as a covering index. Now the
EXPLAIN ANALYSE clearly shows 'Index Only Scan using ar_tots_cover'. I
have no idea what changed.

Here is the new EXPLAIN ANALYSE -

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=161.39..161.51 rows=1 width=132) (actual
time=1.566..1.581 rows=5 loops=1)
   Merge Cond: (a.source_code_id = a_1.source_code_id)
   ->  GroupAggregate  (cost=50.27..50.29 rows=1 width=36) (actual
time=0.226..0.232 rows=5 loops=1)
         Group Key: a.source_code_id
         ->  Sort  (cost=50.27..50.28 rows=1 width=12) (actual
time=0.217..0.218 rows=5 loops=1)
               Sort Key: a.source_code_id
               Sort Method: quicksort  Memory: 25kB
               ->  Subquery Scan on a  (cost=47.78..50.26 rows=1
width=12) (actual time=0.135..0.212 rows=5 loops=1)
                     Filter: (a.row_num = 1)
                     Rows Removed by Filter: 59
                     ->  WindowAgg  (cost=47.78..49.49 rows=62
width=36) (actual time=0.133..0.202 rows=64 loops=1)
                           ->  Sort  (cost=47.78..47.94 rows=62
width=28) (actual time=0.124..0.132 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
                                 ->  Index Only Scan using
ar_tots_cover on ar_totals  (cost=0.28..45.93 rows=62 width=28) (actual
time=0.038..0.094 rows=64 loops=1)
                                       Index Cond: ((ledger_row_id = 1)
AND (tran_date <= '2015-04-30'::date))
                                       Heap Fetches: 0
   ->  GroupAggregate  (cost=111.12..111.17 rows=2 width=36) (actual
time=1.337..1.342 rows=5 loops=1)
         Group Key: a_1.source_code_id
         ->  Sort  (cost=111.12..111.13 rows=3 width=12) (actual
time=1.333..1.334 rows=5 loops=1)
               Sort Key: a_1.source_code_id
               Sort Method: quicksort  Memory: 25kB
               ->  Subquery Scan on a_1  (cost=83.98..111.10 rows=3
width=12) (actual time=0.538..1.328 rows=5 loops=1)
                     Filter: (a_1.row_num = 1)
                     Rows Removed by Filter: 674
                     ->  WindowAgg  (cost=83.98..102.62 rows=678
width=36) (actual time=0.537..1.248 rows=679 loops=1)
                           ->  Sort  (cost=83.98..85.67 rows=678
width=28) (actual time=0.531..0.613 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
                                 ->  Index Only Scan using
ar_tots_cover on ar_totals ar_totals_1  (cost=0.28..52.09 rows=678
width=28) (actual time=0.017..0.270 rows=679 loops=1)
                                       Index Cond: ((ledger_row_id = 1)
AND (tran_date < '2015-09-01'::date))
                                       Heap Fetches: 0
 Planning Time: 0.504 ms
 Execution Time: 1.673 ms
(34 rows)

I am happy to leave this here. Thanks for all the assistance.

Frank