Hi All,
Description of the problem on which, I was working on:
* New plan node types. There are some new node types in 8.4.
The graphical explain tool needs to learn about these, plus
we need new icons for them.
The new plan nodes are:
- Recursive Union
- Nested Loop Semi Join
- Nested Loop Anti Join
- Merge Semi Join
- Merge Anti Join
- Hash Semi Join
- Hash Anti Join
- CTE Scan
- WorkTable Scan
- HashSetOp Intersect
- HashSetOp Intersect All
- HashSetOp Except
- HashSetOp Except All
- HashSetOp ???
- WindowAgg
Please find the patch for that.
I have tried to draw icons for these node-types, but I find myself
a very bad artist. :p
I have also attached the queries, which I have used to test this.
--
Regards,
Ashesh Vashi
EnterpriseDB INDIA: http://www.enterprisedb.com
-- 1
-- CTE Scan
WITH RECURSIVE a(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM a WHERE i < 10) SELECT * FROM a;
-- 2
-- Nested Loop Semi Join
SELECT * FROM (values(1)) AS a(i) WHERE i IN (values(1));
-- 3
-- Hash Semi Join
SET enable_nestloop = off;
SELECT * FROM (values(1)) AS a(i) WHERE i IN (values(1));
-- 4
-- Merge Semi Join
SET enable_nestloop = off;
SET enable_hAShjoin = off;
SELECT * FROM (values(1)) AS a(i) WHERE i IN (values(1));
RESET all;
-- 5
-- HashSetOp Intersect
SELECT * FROM (values(1)) AS a(i) INTERSECT (values(1));
-- 6
-- HashSetOp Intersect All
SELECT * FROM (values(1)) AS a(i) INTERSECT ALL (values(1));
-- 7
-- HashSetOp Except
SELECT * FROM (values(1)) AS a(i) EXCEPT (values(1));
-- 8
-- HashSetOp Except All
SELECT * FROM (values(1)) AS a(i) EXCEPT ALL (values(1));
-- 9
-- Hash Anti Join
CREATE TABLE NewPlanTestTbl1 AS (SELECT * FROM generate_series(1,1000) AS a(i));
ANALYZE NewPlanTestTbl1;
SELECT * FROM (SELECT * FROM generate_series(1,100)) AS a(i) WHERE NOT EXISTS (SELECT 1 FROM NewPlanTestTbl1 WHERE i = a.i);
-- 10
-- Merge Anti Join
SET enable_hAShjoin = off;
SELECT * FROM (SELECT * FROM generate_series(1,100)) AS a(i) WHERE NOT EXISTS (SELECT 1 FROM NewPlanTestTbl1 WHERE i = a.i);
-- 11
-- Nested Loop Anti Join
SET enable_hasHjoin = off;
SET enable_mergejoin = off;
SELECT * FROM (SELECT * FROM generate_series(1,100)) AS a(i) WHERE NOT EXISTS (SELECT 1 FROM NewPlanTestTbl1 WHERE i = a.i);
DROP TABLE NewPlanTestTbl1;
CREATE TABLE NewPlanTestTbl2 (
id INT PRIMARY KEY, -- department ID
parent_NewPlanTestTbl2 INT REFERENCES NewPlanTestTbl2, -- upper department ID
name TEXT -- department name
);
INSERT INTO NewPlanTestTbl2 VALUES (0, NULL, 'ROOT');
INSERT INTO NewPlanTestTbl2 VALUES (1, 0, 'A');
INSERT INTO NewPlanTestTbl2 VALUES (2, 1, 'B');
INSERT INTO NewPlanTestTbl2 VALUES (3, 2, 'C');
INSERT INTO NewPlanTestTbl2 VALUES (4, 2, 'D');
INSERT INTO NewPlanTestTbl2 VALUES (5, 0, 'E');
INSERT INTO NewPlanTestTbl2 VALUES (6, 4, 'F');
INSERT INTO NewPlanTestTbl2 VALUES (7, 5, 'G');
-- NewPlanTestTbl2 structure represented here is AS follows:
--
-- ROOT-+->A-+->B-+->C
-- | |
-- | +->D-+->F
-- +->E-+->G
-- 12
-- Recursive Union
-- WorkTable Scan
WITH RECURSIVE subdepartment AS
(
non recursive term
-- SELECT * FROM NewPlanTestTbl2 WHERE name = 'A'
-- UNION ALL
recursive term
-- SELECT d.* FROM NewPlanTestTbl2 AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
DROP TABLE NewPlanTestTbl2;
-- Window Functions
-- WindowAgg Node Type
SELECT dname, empno, sal, avg(sal) OVER (PARTITION BY dname) FROM empsalary;
SELECT sal, sum(sal) OVER () FROM empsalary;
SELECT sal, sum(sal) OVER (ORDER BY sal) FROM empsalary;
SELECT
dname, empno, sal
FROM
(SELECT
dname, empno, sal,
rank() OVER (PARTITION BY dname ORDER BY sal DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
SELECT
sum(sal) OVER w, avg(sal) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY dname ORDER BY sal DESC);
SELECT * FROM
( SELECT
ROW_NUMBER() OVER (ORDER BY empno ASC) AS row_number,
*
FROM emp
) AS foo
WHERE row_number <= 10;
SELECT * FROM (
SELECT
RANK() OVER (ORDER BY sal ASC) AS ranking,
empno,
ename,
sal
FROM emp
) AS foo
WHERE ranking <= 10;
DROP TABLE IF EXISTS WindowFuncTestTbl;
CREATE TABLE WindowFuncTestTbl(
id NUMERIC(2),
value NUMERIC(6,2)
);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (1,9);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (2,2.11);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (3,3.44);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (4,-4.21);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (8,123.45);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (9,98.23);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (10,938.23);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (11,984.23);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (12,198.23);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (13,928.87);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (14,25.37);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (15,918.3);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (16,9.23);
INSERT INTO WindowFuncTestTbl(ID, value) VALUES (17,8.23);
SELECT
id, value,
AVG(value) OVER(ORDER BY id) ma
FROM WindowFuncTestTbl
ORDER BY id;
DROP TABLE WindowFuncTestTbl;
CREATE TABLE WindowFuncTestTbl(
ID CHARACTER VARYING(32) NOT NULL PRIMARY KEY,
fname CHARACTER VARYING(80),
lname CHARACTER VARYING(80),
joindate DATE,
retiredate DATE,
sal NUMERIC(8,2),
city CHARACTER VARYING(80),
state CHARACTER VARYING(80),
desc CHARACTER VARYING(120)
);
INSERT INTO WindowFuncTestTbl(ID, fname, lname, joindate, retiredate, sal, city, desc) VALUES ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer');
INSERT INTO WindowFuncTestTbl(ID, fname, lname, joindate, retiredate, sal, city, desc) VALUES('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester');
INSERT INTO WindowFuncTestTbl(ID, fname, lname, joindate, retiredate, sal, city, desc) VALUES('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester');
INSERT INTO WindowFuncTestTbl(ID, fname, lname, joindate, retiredate, sal, city, desc) VALUES('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager');
INSERT INTO WindowFuncTestTbl(ID, fname, lname, joindate, retiredate, sal, city, desc) VALUES('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO WindowFuncTestTbl(ID, fname, lname, joindate, retiredate, sal, city, desc) VALUES('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester');
INSERT INTO WindowFuncTestTbl(ID, fname, lname, joindate, retiredate, sal, city, desc) VALUES('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager');
INSERT INTO WindowFuncTestTbl(ID, fname, lname, joindate, retiredate, sal, city, desc) VALUES('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester');
SELECT
city, SUM(sal) AS city_sal,
SUM(SUM(sal)) OVER
(ORDER BY city)
AS cumulative_sal
FROM WindowFuncTestTbl
GROUP BY city
ORDER BY city;
SELECT
city, SUM(sal) AS city_sal,
AVG(SUM(sal)) OVER
(ORDER BY city)
AS moving_average
FROM WindowFuncTestTbl
GROUP BY city
ORDER BY city;
SELECT id, sal,
AVG(sal) OVER(ORDER BY id) ma
FROM WindowFuncTestTbl
ORDER BY id;
SELECT id, sal,
AVG(sal) OVER(ORDER BY id) ma,
SUM(sal) OVER(ORDER BY id) sum,
(SUM(sal) OVER(ORDER BY id))/3 "Sum/3"
FROM WindowFuncTestTbl
ORDER BY id;
SELECT id, sal,
COUNT(sal) OVER(ORDER BY id)
FROM WindowFuncTestTbl
ORDER BY id;
SELECT id, sal,
AVG(sal) OVER(ORDER BY id) ma,
FIRST_VALUE(sal) OVER(ORDER BY id) first,
LAST_VALUE(sal) OVER(ORDER BY id) last
FROM WindowFuncTestTbl
ORDER id;
-- clean the table
DROP TABLE WindowFuncTestTbl;
Index: ctl/explainShape.cpp
===================================================================
--- ctl/explainShape.cpp (revision 7529)
+++ ctl/explainShape.cpp (working copy)
@@ -45,6 +45,26 @@
#define BMP_BORDER 3
+
+// TODO::
+// Remove these defines, when appropriate xpm are generated for them
+#define ex_nested_loop_anti_join_xpm ex_join_xpm
+#define ex_nested_loop_semi_join_xpm ex_join_xpm
+#define ex_merge_anti_join ex_join_xpm
+#define ex_merge_semi_join ex_join_xpm
+#define ex_hash_anti_join_xpm ex_join_xpm
+#define ex_hash_semi_join_xpm ex_join_xpm
+#define ex_hashsetop_except_all_xpm ex_setop_xpm
+#define ex_hashsetop_except_xpm ex_setop_xpm
+#define ex_hashsetop_intersect_all_xpm ex_setop_xpm
+#define ex_hashsetop_intersect_xpm ex_setop_xpm
+#define ex_hashsetop_unknown_xpm ex_setop_xpm
+#define ex_worktable_scan_xpm ex_scan_xpm
+#define ex_cte_scan_xpm ex_scan_xpm
+#define ex_recursive_union_xpm ex_nested_xpm
+#define ex_windowagg_xpm ex_sort_xpm
+
+
ExplainShape::ExplainShape(const char *bmp[], const wxString &description, long tokenNo, long detailNo)
{
SetBitmap(wxBitmap(bmp));
@@ -149,6 +169,9 @@
wxString token = tokens.GetNextToken();
wxString token2 = tokens.GetNextToken();
wxString token3 = tokens.GetNextToken();
+ wxString token4;
+ if (tokens.HasMoreTokens())
+ token4 = tokens.GetNextToken();
wxString descr = costPos > 0 ? str.Left(costPos) : str;
// possible keywords can be found in postgresql/src/backend/commands/explain.c
@@ -157,20 +180,109 @@
else if (token == wxT("Trigger")) return 0;
else if (token == wxT("Result")) s = new ExplainShape(ex_result_xpm, descr);
else if (token == wxT("Append")) s = new ExplainShape(ex_append_xpm, descr);
- else if (token == wxT("Nested")) s = new ExplainShape(ex_nested_xpm, descr);
- else if (token == wxT("Merge")) s = new ExplainShape(ex_merge_xpm, descr);
+ else if (token == wxT("Nested"))
+ {
+ if (token2 == wxT("Loop") && token4 == wxT("Join"))
+ {
+ // Nested Loop Anti Join
+ if (token3 == wxT("Anti"))
+ {
+ s = new ExplainShape(ex_nested_loop_anti_join_xpm, descr);
+ }
+ // Nested Loop Semi Join
+ else
+ {
+ s = new ExplainShape(ex_nested_loop_semi_join_xpm, descr);
+ }
+ }
+ if (!s)
+ s = new ExplainShape(ex_nested_xpm, descr);
+ }
+ else if (token == wxT("Merge"))
+ {
+ if (token3 == wxT("Join"))
+ {
+ // Merge Anti Join
+ if (token2 == wxT("Anti"))
+ {
+ s = new ExplainShape(ex_merge_anti_join, descr);
+ }
+ // Merge Semi Join
+ else
+ {
+ s = new ExplainShape(ex_merge_semi_join, descr);
+ }
+ }
+ else
+ {
+ s = new ExplainShape(ex_merge_xpm, descr);
+ }
+ }
else if (token == wxT("Hash"))
{
if (token2 == wxT("Join"))
+ {
s = new ExplainShape(ex_join_xpm, descr);
+ }
else
{
if (token3 == wxT("Join"))
- s = new ExplainShape(ex_join_xpm, descr);
+ {
+ // Hash Anti Join
+ if (token2 == wxT("Anti"))
+ {
+ s = new ExplainShape(ex_hash_anti_join_xpm, descr);
+ }
+ // Hash Semi Join
+ else if (token2 == wxT("Semi"))
+ {
+ s = new ExplainShape(ex_hash_semi_join_xpm, descr);
+ }
+ else
+ {
+ s = new ExplainShape(ex_hash_xpm, descr);
+ }
+ }
else
+ {
s = new ExplainShape(ex_hash_xpm, descr);
+ }
}
}
+ else if (token == wxT("HashSetOp"))
+ {
+ if (token2 == wxT("Except"))
+ {
+ // HashSetOp Except ALL
+ if (token3 == wxT("ALL"))
+ {
+ s = new ExplainShape(ex_hashsetop_except_all_xpm, descr);
+ }
+ // HashSetOp Except
+ else
+ {
+ s = new ExplainShape(ex_hashsetop_except_xpm, descr);
+ }
+ }
+ else if (token2 == wxT("Intersect"))
+ {
+ // HashSetOp Intersect ALL
+ if (token3 == wxT("ALL"))
+ {
+ s = new ExplainShape(ex_hashsetop_intersect_all_xpm, descr);
+ }
+ // HashSetOp Intersect
+ else
+ {
+ s = new ExplainShape(ex_hashsetop_intersect_xpm, descr);
+ }
+ }
+ else
+ {
+ // HashSetOp ???
+ s = new ExplainShape(ex_hashsetop_unknown_xpm, descr);
+ }
+ }
else if (token == wxT("Subquery")) s = new ExplainShape(ex_subplan_xpm, descr, 0, 2);
else if (token == wxT("Function")) s = new ExplainShape(ex_result_xpm, descr, 0, 2);
else if (token == wxT("Materialize")) s = new ExplainShape(ex_materialize_xpm, descr);
@@ -180,10 +292,7 @@
s = new ExplainShape(ex_aggregate_xpm, descr);
else if (token == wxT("Unique")) s = new ExplainShape(ex_unique_xpm, descr);
else if (token == wxT("SetOp")) s = new ExplainShape(ex_setop_xpm, descr);
-
-
else if (token == wxT("Limit")) s = new ExplainShape(ex_limit_xpm, descr);
-
else if (token == wxT("Bitmap"))
{
if (token2 == wxT("Index")) s = new ExplainShape(ex_bmp_index_xpm, descr, 4, 3);
@@ -192,16 +301,29 @@
else if (token2 == wxT("Scan"))
{
if (token == wxT("Index"))
- {
- if (token3 == wxT("Backward"))
- s = new ExplainShape(ex_index_scan_xpm, descr, 4, 3);
- else
- s = new ExplainShape(ex_index_scan_xpm, descr, 3, 2);
- }
- else if (token == wxT("Tid")) s = new ExplainShape(ex_tid_scan_xpm, descr, 3, 2);
- else s = new ExplainShape(ex_scan_xpm, descr, 3, 2);
+ // Scan Index Backword
+ if (token3 == wxT("Backward"))
+ s = new ExplainShape(ex_index_scan_xpm, descr, 4, 3);
+ else
+ s = new ExplainShape(ex_index_scan_xpm, descr, 3, 2);
+ // Tid Scan
+ else if (token == wxT("Tid"))
+ s = new ExplainShape(ex_tid_scan_xpm, descr, 3, 2);
+ // WorkTable scan
+ else if (token == wxT("WorkTable"))
+ s = new ExplainShape(ex_worktable_scan_xpm, descr, 3, 2);
+ // CTE Scan
+ else if (token == wxT("CTE"))
+ s = new ExplainShape(ex_cte_scan_xpm, descr, 3, 2);
+ else
+ s = new ExplainShape(ex_scan_xpm, descr, 3, 2);
}
else if (token2 == wxT("Seek")) s = new ExplainShape(ex_seek_xpm, descr, 3, 2);
+ // Recursive Union
+ else if (token == wxT("Recursive") && token2 == wxT("Union"))
+ s = new ExplainShape(ex_recursive_union_xpm, descr);
+ else if (token == wxT("WindowAgg"))
+ s = new ExplainShape(ex_windowagg_xpm, descr);
if (!s)
s = new ExplainShape(ex_unknown_xpm, descr);
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
