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)
pá 19. 3. 2021 v 11:58 odesílatel Frank Millman <frank@xxxxxxxxxxxx> napsal:
The most slow operation here is seq scan and sort of ar_totals, but still the 4ms query is pretty fast. Maybe MSSQL server can read data faster. Did you run VACUUM on your table?
MSSQL has a more simple data format - so maybe seq scan can be faster.
