Description:
Hi,
In the following test case, there are two equivalent queries: one is a normal SELECT, and the other is a prepared SELECT. I found the normal SELECT is consistently slower than the prepared SELECT. Because the literal values in prepared statements are not known at planning time, the resulting query plans are often suboptimal and are therefore expected to perform worse than those of normal SELECT statements. This observation suggests that there is still room for improvement in the query plan selection for normal SELECT statements.
```
CREATE TABLE IF NOT EXISTS t0(c0 TINYINT(168)) ;
SET SESSION big_tables = ON;
INSERT IGNORE INTO t0(c0) VALUES(664831670);
SET PROFILING = 1;
EXPLAIN ANALYZE SELECT DISTINCT (CAST( EXISTS (SELECT 1) AS SIGNED)) && ((- ((CAST(-1892716661 AS DECIMAL)) IN ('dw(')))) AS ref0 FROM t0;
-> Limit: 1 row(s) (cost=1.08..1.08 rows=1) (actual time=0.664..0.664 rows=1 loops=1)
-> Table scan on <temporary> (cost=1.08..1.08 rows=1) (actual time=0.662..0.662 rows=1 loops=1)
-> Temporary table (cost=0.58..0.58 rows=1) (actual time=0.66..0.66 rows=1 loops=1)
-> Table scan on t0 (cost=0.35 rows=1) (actual time=0.0066..0.00791 rows=1 loops=1)
-> Select #2 (subquery in projection; run only once)
-> Limit: 1 row(s) (cost=0..0 rows=1) (actual time=630e-6..700e-6 rows=1 loops=1)
-> Rows fetched before execution (cost=0..0 rows=1) (actual time=70e-6..70e-6 rows=1 loops=1)
SET @a = -1892716661;
PREPARE prepare_query FROM "EXPLAIN ANALYZE SELECT DISTINCT (CAST( EXISTS (SELECT 1) AS SIGNED)) && ((- ((CAST(? AS DECIMAL)) IN ('dw(')))) AS ref0 FROM t0";
EXECUTE prepare_query USING @a;
-> Group (no aggregates) (cost=0.58 rows=1) (actual time=0.00769..0.00778 rows=1 loops=1)
-> Table scan on t0 (cost=0.35 rows=1) (actual time=0.00528..0.00654 rows=1 loops=1)
-> Select #2 (subquery in projection; run only once)
-> Limit: 1 row(s) (cost=0..0 rows=1) (actual time=318e-6..389e-6 rows=1 loops=1)
-> Rows fetched before execution (cost=0..0 rows=1) (actual time=70e-6..70e-6 rows=1 loops=1)
```
In addition, we observe that removing the statement "SET SESSION big_tables = ON" significantly improves the execution speed of normal SELECT statements (although they remain slower than prepared SELECT statements), while having no observable effect on prepared SELECT statements.
How to repeat:
```
CREATE TABLE IF NOT EXISTS t0(c0 TINYINT(168)) ;
SET SESSION big_tables = ON;
INSERT IGNORE INTO t0(c0) VALUES(664831670);
SET PROFILING = 1;
EXPLAIN ANALYZE SELECT DISTINCT (CAST( EXISTS (SELECT 1) AS SIGNED)) && ((- ((CAST(-1892716661 AS DECIMAL)) IN ('dw(')))) AS ref0 FROM t0;
SET @a = -1892716661;
PREPARE prepare_query FROM "EXPLAIN ANALYZE SELECT DISTINCT (CAST( EXISTS (SELECT 1) AS SIGNED)) && ((- ((CAST(? AS DECIMAL)) IN ('dw(')))) AS ref0 FROM t0";
EXECUTE prepare_query USING @a;
```
Description: Hi, In the following test case, there are two equivalent queries: one is a normal SELECT, and the other is a prepared SELECT. I found the normal SELECT is consistently slower than the prepared SELECT. Because the literal values in prepared statements are not known at planning time, the resulting query plans are often suboptimal and are therefore expected to perform worse than those of normal SELECT statements. This observation suggests that there is still room for improvement in the query plan selection for normal SELECT statements. ``` CREATE TABLE IF NOT EXISTS t0(c0 TINYINT(168)) ; SET SESSION big_tables = ON; INSERT IGNORE INTO t0(c0) VALUES(664831670); SET PROFILING = 1; EXPLAIN ANALYZE SELECT DISTINCT (CAST( EXISTS (SELECT 1) AS SIGNED)) && ((- ((CAST(-1892716661 AS DECIMAL)) IN ('dw(')))) AS ref0 FROM t0; -> Limit: 1 row(s) (cost=1.08..1.08 rows=1) (actual time=0.664..0.664 rows=1 loops=1) -> Table scan on <temporary> (cost=1.08..1.08 rows=1) (actual time=0.662..0.662 rows=1 loops=1) -> Temporary table (cost=0.58..0.58 rows=1) (actual time=0.66..0.66 rows=1 loops=1) -> Table scan on t0 (cost=0.35 rows=1) (actual time=0.0066..0.00791 rows=1 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit: 1 row(s) (cost=0..0 rows=1) (actual time=630e-6..700e-6 rows=1 loops=1) -> Rows fetched before execution (cost=0..0 rows=1) (actual time=70e-6..70e-6 rows=1 loops=1) SET @a = -1892716661; PREPARE prepare_query FROM "EXPLAIN ANALYZE SELECT DISTINCT (CAST( EXISTS (SELECT 1) AS SIGNED)) && ((- ((CAST(? AS DECIMAL)) IN ('dw(')))) AS ref0 FROM t0"; EXECUTE prepare_query USING @a; -> Group (no aggregates) (cost=0.58 rows=1) (actual time=0.00769..0.00778 rows=1 loops=1) -> Table scan on t0 (cost=0.35 rows=1) (actual time=0.00528..0.00654 rows=1 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit: 1 row(s) (cost=0..0 rows=1) (actual time=318e-6..389e-6 rows=1 loops=1) -> Rows fetched before execution (cost=0..0 rows=1) (actual time=70e-6..70e-6 rows=1 loops=1) ``` In addition, we observe that removing the statement "SET SESSION big_tables = ON" significantly improves the execution speed of normal SELECT statements (although they remain slower than prepared SELECT statements), while having no observable effect on prepared SELECT statements. How to repeat: ``` CREATE TABLE IF NOT EXISTS t0(c0 TINYINT(168)) ; SET SESSION big_tables = ON; INSERT IGNORE INTO t0(c0) VALUES(664831670); SET PROFILING = 1; EXPLAIN ANALYZE SELECT DISTINCT (CAST( EXISTS (SELECT 1) AS SIGNED)) && ((- ((CAST(-1892716661 AS DECIMAL)) IN ('dw(')))) AS ref0 FROM t0; SET @a = -1892716661; PREPARE prepare_query FROM "EXPLAIN ANALYZE SELECT DISTINCT (CAST( EXISTS (SELECT 1) AS SIGNED)) && ((- ((CAST(? AS DECIMAL)) IN ('dw(')))) AS ref0 FROM t0"; EXECUTE prepare_query USING @a; ```