Re: SELECT is faster on SQL Server

pá 19. 3. 2021 v 11:58 odesílatel Frank Millman <frank(at)chagford(dot)com> napsal:

>
> 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)
>
>
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.