Re: SELECT is faster on SQL Server - Mailing list pgsql-general

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