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
