firebird icon indicating copy to clipboard operation
firebird copied to clipboard

Unnecessary sorting of an unused field

Open AlexBekhtin opened this issue 1 year ago • 0 comments

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.

AlexBekhtin avatar Feb 24 '24 17:02 AlexBekhtin