firebird
firebird copied to clipboard
Unnecessary sorting of an unused field
FB 5.0
select distinct '' as small_field
from
(
select cast('' as varchar(8000)) as big_field from rdb$database
) bf
/*
Select Expression
-> Unique Sort (record length: 28, key length: 4)
-> Table "RDB$DATABASE" as "BF RDB$DATABASE" Full Scan
*/
select distinct '' as small_field
from
(
select cast('' as varchar(8000)) as big_field from rdb$database
union all
select cast('' as varchar(8000)) as big_field from rdb$database
) bf
/*
Select Expression
-> Unique Sort (record length: 8044, key length: 4)
-> Union
-> Table "RDB$DATABASE" as "BF RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" as "BF RDB$DATABASE" Full Scan
*/
Unique Sort (record length: 28, key length: 4) vs Unique Sort (record length: 8044, key length: 4)
When using large datasets, the query execution time differs many times over.