Changeset: d89d3d950410 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d89d3d950410 Added Files: sql/test/mergetables/Tests/forexload.sql Modified Files: sql/test/mergetables/Tests/All sql/test/mergetables/Tests/mergequery.sql Branch: default Log Message:
Query enhancements for GIS The mergequery shows the patterns observed by RG that blocks parallel processing over x and y columns. diffs (114 lines): diff --git a/sql/test/mergetables/Tests/All b/sql/test/mergetables/Tests/All --- a/sql/test/mergetables/Tests/All +++ b/sql/test/mergetables/Tests/All @@ -4,3 +4,5 @@ mergequery #mergedrop corrupts the database #crash0 crashes the server +#forex + diff --git a/sql/test/mergetables/Tests/forexload.sql b/sql/test/mergetables/Tests/forexload.sql new file mode 100644 --- /dev/null +++ b/sql/test/mergetables/Tests/forexload.sql @@ -0,0 +1,73 @@ +-- sample application based on partitions +CREATE TABLE day1 ( clk timestamp, currency string, ts timestamp, bid decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6) ); +CREATE TABLE day1stage ( clk bigint, currency string, ts bigint, bid decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6) ); + +COPY 10 RECORDS INTO day1stage FROM stdin USING DELIMITERS '|','\n'; +1413267171000|EUR/USD|1413267158643|1.271810|1.271890|1.271850|0.000080| +1413267171000|USD/JPY|1413267171225|107.121000|107.127000|107.124000|0.006000| +1413267171000|GBP/USD|1413267161304|1.606820|1.606930|1.606875|0.000110| +1413267171000|EUR/GBP|1413267150417|0.791460|0.791570|0.791515|0.000110| +1413267171000|USD/CHF|1413267158643|0.950420|0.950550|0.950485|0.000130| +1413267171000|EUR/JPY|1413267171318|136.240000|136.253000|136.246500|0.013000| +1413267171000|EUR/CHF|1413267158643|1.208760|1.208950|1.208855|0.000190| +1413267171000|USD/CAD|1413267171318|1.121180|1.121270|1.121225|0.000090| +1413267171000|AUD/USD|1413267162803|0.878680|0.878780|0.878730|0.000100| +1413267171000|GBP/JPY|1413267171239|172.126000|172.146000|172.136000|0.020000 + +DROP TABLE day1stagel +ALTER TABLE day1 SET READ ONLY; + +CREATE TABLE day2 ( clk timestamp, currency string, ts timestamp, bid decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6) ); +CREATE TABLE day2stage ( clk bigint, currency string, ts bigint, bid decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6) ); + +COPY 10 RECORDS INTO day2stage FROM stdin USING DELIMITERS '|','\n'; +1413267176000|EUR/USD|1413267177168|1.271780|1.271880|1.271830|0.000100 +1413267176000|USD/JPY|1413267177168|107.120000|107.125000|107.122500|0.005000 +1413267176000|GBP/USD|1413267175356|1.606820|1.606950|1.606885|0.000130 +1413267176000|EUR/GBP|1413267175336|0.791440|0.791550|0.791495|0.000110 +1413267176000|USD/CHF|1413267175367|0.950430|0.950560|0.950495|0.000130 +1413267176000|EUR/JPY|1413267177033|136.235000|136.248000|136.241500|0.013000 +1413267176000|EUR/CHF|1413267158643|1.208760|1.208950|1.208855|0.000190 +1413267176000|USD/CAD|1413267173063|1.121150|1.121260|1.121205|0.000110 +1413267176000|AUD/USD|1413267176076|0.878680|0.878760|0.878720|0.000080 +1413267176000|GBP/JPY|1413267176950|172.122000|172.142000|172.132000|0.020000 + +DROP TABLE day1stagel +ALTER TABLE day1 SET READ ONLY; + +CREATE TABLE day3 ( clk timestamp, currency string, ts timestamp, bid decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6) ); + +CREATE MERGE TABLE forex ( clk bigint, currency string, ts bigint, bid decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6) ); + +ALTER TABLE forex ADD TABLE day1; +ALTER TABLE forex ADD TABLE day2; +ALTER TABLE forex ADD TABLE day3; + +SELECT * FROM forex; + +-- update the last part +INSERT INTO day3 VALUES(1413267181000, 'EUR/USD', 1413267182327, 1.271910, 1.271990, 1.271950, 0.000080); +INSERT INTO day3 VALUES(1413267181000, 'USD/JPY', 1413267181647, 107.114000,107.121000,107.117500,0.007000); +INSERT INTO day3 VALUES(1413267181000, 'GBP/USD', 1413267182048, 1.606870, 1.606980, 1.606925, 0.000110); +INSERT INTO day3 VALUES(1413267181000, 'EUR/GBP', 1413267181968, 0.791490, 0.791600, 0.791545, 0.000110); +INSERT INTO day3 VALUES(1413267181000, 'USD/CHF', 1413267182041, 0.950350, 0.950460, 0.950405, 0.000110); +INSERT INTO day3 VALUES(1413267181000, 'EUR/JPY', 1413267182406, 136.241000,136.253000,136.247000,0.012000); +INSERT INTO day3 VALUES(1413267181000, 'EUR/CHF', 1413267181950, 1.208770, 1.208950, 1.208860, 0.000180); +INSERT INTO day3 VALUES(1413267181000, 'USD/CAD', 1413267181830, 1.121120, 1.121230, 1.121175, 0.000110); +INSERT INTO day3 VALUES(1413267181000, 'AUD/USD', 1413267181549, 0.878730, 0.878810, 0.878770, 0.000080); +INSERT INTO day3 VALUES(1413267181000, 'GBP/JPY', 1413267181618, 172.116000,172.138000,172.127000,0.022000); + +-- perform some compound queries +SELECT avg(bid), sum(bid) FROM forex; +SELECT avg(bid), sum(bid) FROM forex GROUP BY currency; + + +-- drop the first day +ALTER TABLE forex DROP TABLE day1; + +SELECT * FROM forex; + +DROP TABLE forex; +DROP TABLE day1; +DROP TABLE day2; +DROP TABLE day3; diff --git a/sql/test/mergetables/Tests/mergequery.sql b/sql/test/mergetables/Tests/mergequery.sql --- a/sql/test/mergetables/Tests/mergequery.sql +++ b/sql/test/mergetables/Tests/mergequery.sql @@ -26,6 +26,23 @@ SELECT * FROM complete where x>=2.0 AND -- overlap partition queries SELECT * FROM complete where x>=1.0 AND x <=2.0; +-- save the result +CREATE TABLE answ( LIKE complete); +EXPLAIN INSERT INTO answ +SELECT * FROM complete where x>=1.0 AND x <=2.0; +INSERT INTO answ +SELECT * FROM complete where x>=1.0 AND x <=2.0; + +EXPLAIN INSERT INTO answ +SELECT * FROM complete +WHERE x BETWEEN 0 AND 2 AND Y BETWEEN 0 AND 2; +INSERT INTO answ +SELECT * FROM complete +WHERE x BETWEEN 0 AND 2 AND Y BETWEEN 0 AND 2; + +EXPLAIN INSERT INTO answ +SELECT * FROM complete where geom.contains(x,y); + DROP TABLE complete; DROP TABLE part1; DROP TABLE part2; _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
