Push the predicate into derived tables or CTE with a window function if it uses
the column on which the partitioning occurs
---------------------------------------------------------------------------------------------------------------------------
Key: CORE-4591
URL: http://tracker.firebirdsql.org/browse/CORE-4591
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Environment: isql WI-T3.0.0.31385 Firebird 3.0 Beta 1
Reporter: Simonov Denis
Push the predicate into derived tables or CTE with a window function if it uses
the column on which the partitioning occurs.
This is possible because it does not distort the result. The only condition is
that all the windows were partitioned on the same columns.
CONNECT 'localhost:test' USER 'SYSDBA' PASSWORD 'masterkey';
SET AUTODDL ON;
RECREATE TABLE T (
ID INT NOT NULL,
A INT
);
SET TERM ^;
EXECUTE BLOCK
AS
DECLARE I INT = 1000000;
BEGIN
WHILE (I > 0) DO
BEGIN
INSERT INTO T(ID, A)
VALUES (:I, MOD(:I, 31));
I = I - 1;
END
END^
SET TERM ;^
COMMIT;
ALTER TABLE T
ADD CONSTRAINT PK_T PRIMARY KEY (ID);
CREATE INDEX IDX_T_A ON T(A);
SET STAT ON;
SET EXPLAIN ON;
OUTPUT NULL;
WITH DT AS (
SELECT
SUM(ID) OVER(PARTITION BY A) AS S,
A
FROM T
)
SELECT S
FROM DT
WHERE A = 1
ROWS 1;
Select Expression
-> First N Records
-> Filter
-> Window
-> Record Buffer (record length: 49)
-> Sort (record length: 52, key length: 8)
-> Window
-> Record Buffer (record length: 25)
-> Table "T" as "DT T" Full Scan
Current memory = 85008912
Delta memory = 51144
Max memory = 153273472
Elapsed time= 5.909 sec
Buffers = 16384
Reads = 0
Writes = 0
Fetches = 2026507
WITH DT AS (
SELECT
SUM(ID) OVER(PARTITION BY A) AS S,
A
FROM T
WHERE A = 1
)
SELECT S
FROM DT
ROWS 1;
Select Expression
-> First N Records
-> Window
-> Record Buffer (record length: 49)
-> Sort (record length: 52, key length: 8)
-> Window
-> Record Buffer (record length: 25)
-> Filter
-> Table "T" as "DT T" Access By ID
-> Bitmap
-> Index "IDX_T_A" Range Scan (full
match)
Current memory = 85431800
Delta memory = 422888
Max memory = 153273472
Elapsed time= 0.179 sec
Buffers = 16384
Reads = 0
Writes = 0
Fetches = 64568
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
