Bug 3590

Summary: string concat on a column is expensive
Product: SQL Reporter: Stefan de Konink <stefan>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement    
Priority: Normal    
Version: -- development   
Hardware: Other   
OS: Linux   

Description Stefan de Konink 2014-09-30 23:47:15 CEST
User-Agent:       Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2166.2 Safari/537.36
Build Identifier: 

For presentation I would like to extract an hour and minute from a timestamp. I noticed that the operation added a lot of overhead to the query, bringing it back to fundamentally: a string concat is extremely expensive.



Reproducible: Always

Steps to Reproduce:
1. a table with roughly 10.000.000 rows
2. select row1||row1 from bigtable limit 1; (~4s)
3. select row1 from bigtable limit 1; (~2ms)

Is it possible to get the topn before the creation of the column as if the rendering is merely a presentation aspect (final operation).
Comment 1 Stefan de Konink 2014-09-30 23:50:30 CEST
I noticed that the problem is very type dependent;

select dataownercode||dataownercode from kv6 limit 1;
+----------------------+
| concat_dataownercode |
+======================+
| HTMHTM               |
+----------------------+
1 tuple (623.450ms)

sql>select source||source from kv6 limit 1;
+----------------+
| concat_source  |
+================+
| VEHICLEVEHICLE |
+----------------+
1 tuple (773.036ms)

sql>select rd_x||rd_x from kv6 limit 1;
+-------------+
| concat_rd_x |
+=============+
| 7806678066  |
+-------------+
1 tuple (1.7s)

select operatingday||operatingday from kv6 limit 1;
+----------------------+
| concat_operatingday  |
+======================+
| 2014-09-142014-09-14 |
+----------------------+
1 tuple (2.8s)

select receive||receive from kv6 limit 1;
+------------------------------------------------------+
| concat_receive                                       |
+======================================================+
| 2014-09-14 19:22:25.1090002014-09-14 19:22:25.109000 |
+------------------------------------------------------+
1 tuple (4.7s)