-- ---------------------------------------------------------------------- -- Test: setup.sql -- ---------------------------------------------------------------------- create schema qp_gist_indexes2; set search_path to qp_gist_indexes2; -- start_ignore create language plpythonu; -- end_ignore create or replace function count_index_scans(explain_query text) returns int as $$ rv = plpy.execute(explain_query) search_text = 'Index Scan' result = 0 for i in range(len(rv)): cur_line = rv[i]['QUERY PLAN'] if search_text.lower() in cur_line.lower(): result = result+1 return result $$ language plpythonu; -- ---------------------------------------------------------------------- -- Test: test01create_table.sql -- ---------------------------------------------------------------------- CREATE TABLE GistTable1 ( id INTEGER, owner VARCHAR, description VARCHAR, property BOX, poli POLYGON, bullseye CIRCLE, v VARCHAR, t TEXT, f FLOAT, p POINT, c CIRCLE, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) DISTRIBUTED BY (id); COPY GistTable1 FROM '@abs_srcdir@/data/PropertyInfo.txt' CSV ; -- ---------------------------------------------------------------------- -- Test: test03IndexScan.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- CREATE INDEX propertyBoxIndex ON GistTable1 USING Gist (property); -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (66, 'Miller', 'Lubbock or leave it', '((3, 1300), (33, 1330))', '( (66,660), (67, 650), (68, 660) )', '( (66, 66), 66)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property ~= '((7052,250),(6050,20))'; owner | property ---------+---------------------- Hypatia | (7052,250),(6050,20) Patty | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= ' ( ( 7052, 250 ) , (6050, 20) )'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; owner | property ---------+---------------------- Hypatia | (7052,250),(6050,20) Patty | (7052,250),(6050,20) (2 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; QUERY PLAN --------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Index Scan using propertyboxindex on gisttable1 Index Cond: (property ~= '(7052,250),(6050,20)'::box) Filter: (property ~= '(7052,250),(6050,20)'::box) Optimizer: PQO version 2.74.0 (5 rows) SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id; id | property ----+---------- 8 | (1 row) -- Alter the table and see if we get the same results. ALTER TABLE GistTable1 CLUSTER ON propertyBoxIndex; SELECT owner, property FROM GistTable1 WHERE property ~= '((7052,250),(6050,20))'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= ' ( ( 7052, 250 ) , (6050, 20) )'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; owner | property ---------+---------------------- Hypatia | (7052,250),(6050,20) Patty | (7052,250),(6050,20) (2 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; QUERY PLAN --------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Index Scan using propertyboxindex on gisttable1 Index Cond: (property ~= '(7052,250),(6050,20)'::box) Filter: (property ~= '(7052,250),(6050,20)'::box) Optimizer: PQO version 2.74.0 (5 rows) SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id; id | property ----+---------- 8 | (1 row) -- ---------------------------------------------------------------------- -- Test: test04Insert.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Insert more data. -- ---------------------------------------------------------------------------- -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (76, 'James McMurtry', 'Levelland', '((1500, 1500), (1700, 1900))', '( (76, 77), (76, 75), (75, 77) )', '( (76, 76), 76)' ); -- ---------------------------------------------------------------------- -- Test: test05Select.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 2 | Theodore Turner | a ranch and reserve | (2100,2100),(2000,2000) | ((1,1),(2,1),(1,2)) | <(2,2),2> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Levelland | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | <(76,76),76> 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- ---------------------------------------------------------------------- -- Test: test06IllegalonAO.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; ALTER TABLE GistTable1 CLUSTER ON propertyBoxIndex; ALTER INDEX propertyBoxIndex RENAME TO propIndex; UPDATE GistTable1 SET description = 'Where''s Johnny?', bullseye = NULL WHERE owner = 'James McMurtry'; -- We should no longer be able to find "Levelland". SELECT property FROM GistTable1 WHERE description = 'Levelland'; property ---------- (0 rows) ALTER INDEX propIndex SET (FILLFACTOR=50); SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) REINDEX INDEX propIndex; SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) -- Delete Theodore Turner DELETE from GistTable1 WHERE property ~= '((2000,2000), (2100, 2100))'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Where's Johnny? | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- Update James McMurtry. UPDATE GistTable1 SET owner = 'Record Company', description = 'fat profit', property = '((100,100), (200, 200))' WHERE property ~= '( (1700,1900),(1500,1500) )'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) ALTER INDEX propIndex RENAME TO propertyBoxIndex; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) DELETE FROM GistTable1 WHERE bullseye = '( (76, 76), 76)'; -- ---------------------------------------------------------------------- -- Test: test07select.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (18 rows) -- ---------------------------------------------------------------------- -- Test: test10MultipleColumns.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test multi-column indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Insert 2 more records, but insert them with the same value for BULLSEYE. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (212, 'Fahrenheit', 'Slightly north of Hades', '( (212, 212), (32, 32) )', '( (212, 212), (600, 600), (70, 70) )', '( (100,100), 212 )' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (100, 'Celsius', 'Barely north of Hades', '( (100, 100), (0, 0) )', '( (100, 100), (600, 600), (70, 70) )', '( (100,100), 212 )' ); -- This should create an index that has duplicate entries for at least one -- value of bullseye. CREATE INDEX i2 ON GistTable1 USING GIST(bullseye); CREATE INDEX i3 ON GistTable1 USING GIST(poli, bullseye); -- This should return 2 rows. SELECT id FROM GistTable1 WHERE bullseye ~= '( (100,100), 212 )'; id ----- 100 212 (2 rows) -- This should return 1 row. SELECT id FROM GistTable1 WHERE property ~= '( (212, 212), (32, 32) )'; id ----- 212 (1 row) -- ---------------------------------------------------------------------- -- Test: test11WherePredicate.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Test GiST indexes with the WHERE predicate in the CREATE INDEX -- statement. This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- -- Add another record that has NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (77, 'S. T. "Rip" Sunset', 'Lost Vegas',NULL, '( (77, 77), (76, 78), (78, 76) )', '( (77, 77), 77)' ); CREATE INDEX propertyIsNullIndex ON GistTable1 USING Gist (property) WHERE property IS NULL; -- Add two more records that have NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (86, 'A. Gent', 'Washingtoon D.C.',NULL, '( (86, 86), (85, 87), (87, 85) )', '( (86, 86), 86)' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (99, 'FelDon Adams', 'Washingtoon D.C.',NULL, '( (99, 99), (97, 98), (98, 97) )', '( (99, 99), 99)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. set optimizer_trace_fallback = TRUE; SELECT owner, property FROM GistTable1 WHERE property IS NULL; INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: No plan has been computed for required properties owner | property -----------------------------+---------- A. Gent | Neil, Nell, and Noel Newall | FelDon Adams | S. T. "Rip" Sunset | (4 rows) SELECT count_index_scans('EXPLAIN SELECT owner, property FROM GistTable1 WHERE property IS NULL ORDER BY id;'); INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: SIRV functions INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: No plan has been computed for required properties CONTEXT: SQL statement "EXPLAIN SELECT owner, property FROM GistTable1 WHERE property IS NULL ORDER BY id;" PL/Python function "count_index_scans" count_index_scans ------------------- 1 (1 row) --start_ignore EXPLAIN SELECT owner, property FROM GistTable1 WHERE property IS NULL ORDER BY id ; INFO: GPORCA failed to produce a plan, falling back to planner QUERY PLAN ----------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=701.28..701.28 rows=2 width=51) Merge Key: id -> Sort (cost=701.28..701.28 rows=1 width=51) Sort Key: id -> Index Scan using propertyisnullindex on gisttable1 (cost=0.00..701.27 rows=1 width=51) Index Cond: (property IS NULL) Optimizer: Postgres query optimizer (7 rows) --end_ignore -- Alter the table and see if we get the same results. ALTER TABLE GistTable1 CLUSTER ON propertyBoxIndex; SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id ; INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: No plan has been computed for required properties id | property ----+---------- 8 | 77 | 86 | 99 | (4 rows) SELECT count_index_scans('EXPLAIN SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id;'); INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: SIRV functions INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: No plan has been computed for required properties CONTEXT: SQL statement "EXPLAIN SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id;" PL/Python function "count_index_scans" count_index_scans ------------------- 1 (1 row) --start_ignore EXPLAIN SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id ; INFO: GPORCA failed to produce a plan, falling back to planner QUERY PLAN ----------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=701.28..701.28 rows=2 width=36) Merge Key: id -> Sort (cost=701.28..701.28 rows=1 width=36) Sort Key: id -> Index Scan using propertyisnullindex on gisttable1 (cost=0.00..701.27 rows=1 width=36) Index Cond: (property IS NULL) Optimizer: Postgres query optimizer (7 rows) --end_ignore reset optimizer_trace_fallback; -- ---------------------------------------------------------------------- -- Test: test13Vacuum.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test VACUUM on GiST indexes. -- Also test somewhat larger data sets than most of my other GiST index -- tests. -- -- This test suite is for AO (Append-Only) and CO (Column-Oriented) -- tables as well as heap tables, so I removed statement(s) such as -- DELETE that can't be executed on AO and CO tables. -- ---------------------------------------------------------------------------- CREATE TABLE GistTable13 ( id INTEGER, property BOX, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) DISTRIBUTED BY (id); -- Register a function that converts TEXT to BOX data type. CREATE FUNCTION TO_BOX(TEXT) RETURNS BOX AS $$ SELECT box_in(textout($1)) $$ LANGUAGE SQL; CREATE FUNCTION insertIntoGistTable13 (seed INTEGER) RETURNS VOID AS $$ DECLARE str1 VARCHAR; ss VARCHAR; s2 VARCHAR; BEGIN ss = CAST(seed AS VARCHAR); s2 = CAST((seed - 1) AS VARCHAR); str1 = '((' || ss || ', ' || ss || '), (' || s2 || ', ' || s2 || '))'; INSERT INTO GistTable13(id, property) VALUES (seed, TO_BOX(CAST(str1 AS TEXT)) ); END; $$ LANGUAGE PLPGSQL ; CREATE FUNCTION insertManyIntoGistTable13 (startValue INTEGER, endValue INTEGER) RETURNS VOID AS $$ DECLARE i INTEGER; BEGIN i = startValue; WHILE i <= endValue LOOP PERFORM insertIntoGistTable13(i); i = i + 1; END LOOP; END; $$ LANGUAGE PLPGSQL ; -- Add some rows before we create the index. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) -- Create the index. CREATE INDEX GistIndex13 ON GistTable13 USING GiST (property); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Add more rows after we create the index. SELECT insertManyIntoGistTable13(1001, 2000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values (coordinates), not just the -- AREA, are the same. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Index Scan using gistindex13 on gisttable13 Index Cond: (property ~= '(999,999),(998,998)'::box) Filter: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (5 rows) VACUUM GistTable13; ANALYZE GistTable13; SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Index Scan using gistindex13 on gisttable13 Index Cond: (property ~= '(999,999),(998,998)'::box) Filter: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (5 rows) TRUNCATE TABLE GistTable13; -- Add some rows. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values are the same, not just the -- same AREA. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Index Scan using gistindex13 on gisttable13 Index Cond: (property ~= '(999,999),(998,998)'::box) Filter: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (5 rows) -- ---------------------------------------------------------------------- -- Test: test15ReindexDropIndex.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- REINDEX -- DROP INDEX -- NOTES: -- Although we seemingly ignore the output of the EXPLAIN statements, -- elsewhere in this test we look for "Index Scan on propertyBoxIndex" -- or something similar in order to verify that the index was used. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Index Scan using propertyboxindex on gisttable1 Index Cond: (property ~= '(3,4),(1,2)'::box) Filter: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (9 rows) REINDEX INDEX propertyBoxIndex; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Index Scan using propertyboxindex on gisttable1 Index Cond: (property ~= '(3,4),(1,2)'::box) Filter: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (9 rows) DROP INDEX propertyBoxIndex; -- Obviously, this shouldn't use the index now that the index is gone. set optimizer_enable_tablescan = TRUE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN -------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Seq Scan on gisttable1 Filter: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (7 rows) -- ---------------------------------------------------------------------- -- Test: teardown.sql -- ---------------------------------------------------------------------- -- start_ignore drop schema qp_gist_indexes2 cascade; NOTICE: drop cascades to function insertmanyintogisttable13(integer,integer) NOTICE: drop cascades to function insertintogisttable13(integer) NOTICE: drop cascades to function to_box(text) NOTICE: drop cascades to table gisttable13 NOTICE: drop cascades to table gisttable1 -- end_ignore -- ---------------------------------------------------------------------- -- Test: setup.sql -- ---------------------------------------------------------------------- -- start_ignore create schema qp_gist_indexes2; set search_path to qp_gist_indexes2; -- end_ignore -- ---------------------------------------------------------------------- -- Test: test01create_table.sql -- ---------------------------------------------------------------------- CREATE TABLE GistTable1 ( id INTEGER, owner VARCHAR, description VARCHAR, property BOX, poli POLYGON, bullseye CIRCLE, v VARCHAR, t TEXT, f FLOAT, p POINT, c CIRCLE, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) WITH (APPENDONLY=True, COMPRESSTYPE=ZLIB, COMPRESSLEVEL=1) DISTRIBUTED BY (id); COPY GistTable1 FROM '@abs_srcdir@/data/PropertyInfo.txt' CSV ; -- ---------------------------------------------------------------------- -- Test: test03IndexScan.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- CREATE INDEX propertyBoxIndex ON GistTable1 USING Gist (property); -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (66, 'Miller', 'Lubbock or leave it', '((3, 1300), (33, 1330))', '( (66,660), (67, 650), (68, 660) )', '( (66, 66), 66)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property ~= '((7052,250),(6050,20))'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= ' ( ( 7052, 250 ) , (6050, 20) )'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; QUERY PLAN --------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(7052,250),(6050,20)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(7052,250),(6050,20)'::box) Optimizer: PQO version 2.74.0 (6 rows) SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id; id | property ----+---------- 8 | (1 row) -- ---------------------------------------------------------------------- -- Test: test04Insert.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Insert more data. -- ---------------------------------------------------------------------------- -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (76, 'James McMurtry', 'Levelland', '((1500, 1500), (1700, 1900))', '( (76, 77), (76, 75), (75, 77) )', '( (76, 76), 76)' ); -- ---------------------------------------------------------------------- -- Test: test05Select.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 2 | Theodore Turner | a ranch and reserve | (2100,2100),(2000,2000) | ((1,1),(2,1),(1,2)) | <(2,2),2> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Levelland | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | <(76,76),76> 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- ---------------------------------------------------------------------- -- Test: test06IllegalonAO.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; ALTER INDEX propertyBoxIndex RENAME TO propIndex; UPDATE GistTable1 SET description = 'Where''s Johnny?', bullseye = NULL WHERE owner = 'James McMurtry'; -- We should no longer be able to find "Levelland". SELECT property FROM GistTable1 WHERE description = 'Levelland'; property ---------- (0 rows) ALTER INDEX propIndex SET (FILLFACTOR=50); SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) REINDEX INDEX propIndex; SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) -- Delete Theodore Turner DELETE from GistTable1 WHERE property ~= '((2000,2000), (2100, 2100))'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Where's Johnny? | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- Update James McMurtry. UPDATE GistTable1 SET owner = 'Record Company', description = 'fat profit', property = '((100,100), (200, 200))' WHERE property ~= '( (1700,1900),(1500,1500) )'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) ALTER INDEX propIndex RENAME TO propertyBoxIndex; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) DELETE FROM GistTable1 WHERE bullseye = '( (76, 76), 76)'; -- ---------------------------------------------------------------------- -- Test: test07select.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (18 rows) -- ---------------------------------------------------------------------- -- Test: test10MultipleColumns.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test multi-column indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Insert 2 more records, but insert them with the same value for BULLSEYE. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (212, 'Fahrenheit', 'Slightly north of Hades', '( (212, 212), (32, 32) )', '( (212, 212), (600, 600), (70, 70) )', '( (100,100), 212 )' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (100, 'Celsius', 'Barely north of Hades', '( (100, 100), (0, 0) )', '( (100, 100), (600, 600), (70, 70) )', '( (100,100), 212 )' ); -- This should create an index that has duplicate entries for at least one -- value of bullseye. CREATE INDEX i2 ON GistTable1 USING GIST(bullseye); CREATE INDEX i3 ON GistTable1 USING GIST(poli, bullseye); -- This should return 2 rows. SELECT id FROM GistTable1 WHERE bullseye ~= '( (100,100), 212 )'; id ----- 100 212 (2 rows) -- This should return 1 row. SELECT id FROM GistTable1 WHERE property ~= '( (212, 212), (32, 32) )'; id ----- 212 (1 row) -- ---------------------------------------------------------------------- -- Test: test11WherePredicate.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Test GiST indexes with the WHERE predicate in the CREATE INDEX -- statement. This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- -- Add another record that has NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (77, 'S. T. "Rip" Sunset', 'Lost Vegas',NULL, '( (77, 77), (76, 78), (78, 76) )', '( (77, 77), 77)' ); CREATE INDEX propertyIsNullIndex ON GistTable1 USING Gist (property) WHERE property IS NULL; -- Add two more records that have NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (86, 'A. Gent', 'Washingtoon D.C.',NULL, '( (86, 86), (85, 87), (87, 85) )', '( (86, 86), 86)' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (99, 'FelDon Adams', 'Washingtoon D.C.',NULL, '( (99, 99), (97, 98), (98, 97) )', '( (99, 99), 99)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property IS NULL; owner | property -----------------------------+---------- S. T. "Rip" Sunset | Neil, Nell, and Noel Newall | FelDon Adams | A. Gent | (4 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property IS NULL ORDER BY id ; QUERY PLAN ------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property IS NULL) -> Bitmap Index Scan on propertyisnullindex Index Cond: (property IS NULL) Optimizer: Postgres query optimizer (9 rows) -- ---------------------------------------------------------------------- -- Test: test13Vacuum.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test VACUUM on GiST indexes. -- Also test somewhat larger data sets than most of my other GiST index -- tests. -- -- This test suite is for AO (Append-Only) and CO (Column-Oriented) -- tables as well as heap tables, so I removed statement(s) such as -- DELETE that can't be executed on AO and CO tables. -- ---------------------------------------------------------------------------- CREATE TABLE GistTable13 ( id INTEGER, property BOX, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) WITH (APPENDONLY=True, COMPRESSTYPE=ZLIB, COMPRESSLEVEL=1) DISTRIBUTED BY (id); -- Register a function that converts TEXT to BOX data type. CREATE FUNCTION TO_BOX(TEXT) RETURNS BOX AS $$ SELECT box_in(textout($1)) $$ LANGUAGE SQL; CREATE FUNCTION insertIntoGistTable13 (seed INTEGER) RETURNS VOID AS $$ DECLARE str1 VARCHAR; ss VARCHAR; s2 VARCHAR; BEGIN ss = CAST(seed AS VARCHAR); s2 = CAST((seed - 1) AS VARCHAR); str1 = '((' || ss || ', ' || ss || '), (' || s2 || ', ' || s2 || '))'; INSERT INTO GistTable13(id, property) VALUES (seed, TO_BOX(CAST(str1 AS TEXT)) ); END; $$ LANGUAGE PLPGSQL ; CREATE FUNCTION insertManyIntoGistTable13 (startValue INTEGER, endValue INTEGER) RETURNS VOID AS $$ DECLARE i INTEGER; BEGIN i = startValue; WHILE i <= endValue LOOP PERFORM insertIntoGistTable13(i); i = i + 1; END LOOP; END; $$ LANGUAGE PLPGSQL ; -- Add some rows before we create the index. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) -- Create the index. CREATE INDEX GistIndex13 ON GistTable13 USING GiST (property); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Add more rows after we create the index. SELECT insertManyIntoGistTable13(1001, 2000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values (coordinates), not just the -- AREA, are the same. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) VACUUM GistTable13; ANALYZE GistTable13; SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) TRUNCATE TABLE GistTable13; -- Add some rows. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values are the same, not just the -- same AREA. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) -- ---------------------------------------------------------------------- -- Test: test15ReindexDropIndex.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- REINDEX -- DROP INDEX -- NOTES: -- Although we seemingly ignore the output of the EXPLAIN statements, -- elsewhere in this test we look for "Index Scan on propertyBoxIndex" -- or something similar in order to verify that the index was used. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(3,4),(1,2)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (10 rows) REINDEX INDEX propertyBoxIndex; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(3,4),(1,2)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (10 rows) DROP INDEX propertyBoxIndex; -- Obviously, this shouldn't use the index now that the index is gone. SET optimizer_enable_tablescan = TRUE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN -------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Seq Scan on gisttable1 Filter: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (7 rows) -- ---------------------------------------------------------------------- -- Test: teardown.sql -- ---------------------------------------------------------------------- -- start_ignore drop schema qp_gist_indexes2 cascade; NOTICE: drop cascades to function insertmanyintogisttable13(integer,integer) NOTICE: drop cascades to function insertintogisttable13(integer) NOTICE: drop cascades to function to_box(text) NOTICE: drop cascades to append only table gisttable13 NOTICE: drop cascades to append only table gisttable1 -- end_ignore -- ---------------------------------------------------------------------- -- Test: setup.sql -- ---------------------------------------------------------------------- -- start_ignore create schema qp_gist_indexes2; set search_path to qp_gist_indexes2; -- end_ignore -- ---------------------------------------------------------------------- -- Test: test01create_table.sql -- ---------------------------------------------------------------------- CREATE TABLE GistTable1 ( id INTEGER, owner VARCHAR, description VARCHAR, property BOX, poli POLYGON, bullseye CIRCLE, v VARCHAR, t TEXT, f FLOAT, p POINT, c CIRCLE, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) WITH (APPENDONLY=True) DISTRIBUTED BY (id); COPY GistTable1 FROM '@abs_srcdir@/data/PropertyInfo.txt' CSV ; -- ---------------------------------------------------------------------- -- Test: test03IndexScan.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- CREATE INDEX propertyBoxIndex ON GistTable1 USING Gist (property); -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (66, 'Miller', 'Lubbock or leave it', '((3, 1300), (33, 1330))', '( (66,660), (67, 650), (68, 660) )', '( (66, 66), 66)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property ~= '((7052,250),(6050,20))'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= ' ( ( 7052, 250 ) , (6050, 20) )'; owner | property ---------+---------------------- Hypatia | (7052,250),(6050,20) Patty | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; owner | property ---------+---------------------- Hypatia | (7052,250),(6050,20) Patty | (7052,250),(6050,20) (2 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; QUERY PLAN --------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(7052,250),(6050,20)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(7052,250),(6050,20)'::box) Optimizer: PQO version 2.74.0 (6 rows) SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id; id | property ----+---------- 8 | (1 row) -- ---------------------------------------------------------------------- -- Test: test04Insert.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Insert more data. -- ---------------------------------------------------------------------------- -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (76, 'James McMurtry', 'Levelland', '((1500, 1500), (1700, 1900))', '( (76, 77), (76, 75), (75, 77) )', '( (76, 76), 76)' ); -- ---------------------------------------------------------------------- -- Test: test05Select.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 2 | Theodore Turner | a ranch and reserve | (2100,2100),(2000,2000) | ((1,1),(2,1),(1,2)) | <(2,2),2> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Levelland | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | <(76,76),76> 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- ---------------------------------------------------------------------- -- Test: test06IllegalonAO.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; ALTER INDEX propertyBoxIndex RENAME TO propIndex; UPDATE GistTable1 SET description = 'Where''s Johnny?', bullseye = NULL WHERE owner = 'James McMurtry'; -- We should no longer be able to find "Levelland". SELECT property FROM GistTable1 WHERE description = 'Levelland'; property ---------- (0 rows) ALTER INDEX propIndex SET (FILLFACTOR=50); SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) REINDEX INDEX propIndex; SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) -- Delete Theodore Turner DELETE from GistTable1 WHERE property ~= '((2000,2000), (2100, 2100))'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Where's Johnny? | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- Update James McMurtry. UPDATE GistTable1 SET owner = 'Record Company', description = 'fat profit', property = '((100,100), (200, 200))' WHERE property ~= '( (1700,1900),(1500,1500) )'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) ALTER INDEX propIndex RENAME TO propertyBoxIndex; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) DELETE FROM GistTable1 WHERE bullseye = '( (76, 76), 76)'; -- ---------------------------------------------------------------------- -- Test: test07select.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (18 rows) -- ---------------------------------------------------------------------- -- Test: test10MultipleColumns.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test multi-column indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Insert 2 more records, but insert them with the same value for BULLSEYE. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (212, 'Fahrenheit', 'Slightly north of Hades', '( (212, 212), (32, 32) )', '( (212, 212), (600, 600), (70, 70) )', '( (100,100), 212 )' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (100, 'Celsius', 'Barely north of Hades', '( (100, 100), (0, 0) )', '( (100, 100), (600, 600), (70, 70) )', '( (100,100), 212 )' ); -- This should create an index that has duplicate entries for at least one -- value of bullseye. CREATE INDEX i2 ON GistTable1 USING GIST(bullseye); CREATE INDEX i3 ON GistTable1 USING GIST(poli, bullseye); -- This should return 2 rows. SELECT id FROM GistTable1 WHERE bullseye ~= '( (100,100), 212 )'; id ----- 212 100 (2 rows) -- This should return 1 row. SELECT id FROM GistTable1 WHERE property ~= '( (212, 212), (32, 32) )'; id ----- 212 (1 row) -- ---------------------------------------------------------------------- -- Test: test11WherePredicate.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Test GiST indexes with the WHERE predicate in the CREATE INDEX -- statement. This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- -- Add another record that has NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (77, 'S. T. "Rip" Sunset', 'Lost Vegas',NULL, '( (77, 77), (76, 78), (78, 76) )', '( (77, 77), 77)' ); CREATE INDEX propertyIsNullIndex ON GistTable1 USING Gist (property) WHERE property IS NULL; -- Add two more records that have NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (86, 'A. Gent', 'Washingtoon D.C.',NULL, '( (86, 86), (85, 87), (87, 85) )', '( (86, 86), 86)' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (99, 'FelDon Adams', 'Washingtoon D.C.',NULL, '( (99, 99), (97, 98), (98, 97) )', '( (99, 99), 99)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property IS NULL; owner | property -----------------------------+---------- S. T. "Rip" Sunset | A. Gent | Neil, Nell, and Noel Newall | FelDon Adams | (4 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property IS NULL ORDER BY id ; QUERY PLAN ------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property IS NULL) -> Bitmap Index Scan on propertyisnullindex Index Cond: (property IS NULL) Optimizer: Postgres query optimizer (9 rows) -- ---------------------------------------------------------------------- -- Test: test13Vacuum.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test VACUUM on GiST indexes. -- Also test somewhat larger data sets than most of my other GiST index -- tests. -- -- This test suite is for AO (Append-Only) and CO (Column-Oriented) -- tables as well as heap tables, so I removed statement(s) such as -- DELETE that can't be executed on AO and CO tables. -- ---------------------------------------------------------------------------- CREATE TABLE GistTable13 ( id INTEGER, property BOX, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) WITH (APPENDONLY=True) DISTRIBUTED BY (id); -- Register a function that converts TEXT to BOX data type. CREATE FUNCTION TO_BOX(TEXT) RETURNS BOX AS $$ SELECT box_in(textout($1)) $$ LANGUAGE SQL; CREATE FUNCTION insertIntoGistTable13 (seed INTEGER) RETURNS VOID AS $$ DECLARE str1 VARCHAR; ss VARCHAR; s2 VARCHAR; BEGIN ss = CAST(seed AS VARCHAR); s2 = CAST((seed - 1) AS VARCHAR); str1 = '((' || ss || ', ' || ss || '), (' || s2 || ', ' || s2 || '))'; INSERT INTO GistTable13(id, property) VALUES (seed, TO_BOX(CAST(str1 AS TEXT)) ); END; $$ LANGUAGE PLPGSQL ; CREATE FUNCTION insertManyIntoGistTable13 (startValue INTEGER, endValue INTEGER) RETURNS VOID AS $$ DECLARE i INTEGER; BEGIN i = startValue; WHILE i <= endValue LOOP PERFORM insertIntoGistTable13(i); i = i + 1; END LOOP; END; $$ LANGUAGE PLPGSQL ; -- Add some rows before we create the index. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) -- Create the index. CREATE INDEX GistIndex13 ON GistTable13 USING GiST (property); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Add more rows after we create the index. SELECT insertManyIntoGistTable13(1001, 2000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values (coordinates), not just the -- AREA, are the same. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) VACUUM GistTable13; ANALYZE GistTable13; SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) TRUNCATE TABLE GistTable13; -- Add some rows. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values are the same, not just the -- same AREA. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) -- ---------------------------------------------------------------------- -- Test: test15ReindexDropIndex.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- REINDEX -- DROP INDEX -- NOTES: -- Although we seemingly ignore the output of the EXPLAIN statements, -- elsewhere in this test we look for "Index Scan on propertyBoxIndex" -- or something similar in order to verify that the index was used. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(3,4),(1,2)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (10 rows) REINDEX INDEX propertyBoxIndex; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(3,4),(1,2)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (10 rows) DROP INDEX propertyBoxIndex; -- Obviously, this shouldn't use the index now that the index is gone. set optimizer_enable_tablescan = TRUE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN -------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Seq Scan on gisttable1 Filter: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (7 rows) -- ---------------------------------------------------------------------- -- Test: teardown.sql -- ---------------------------------------------------------------------- -- start_ignore drop schema qp_gist_indexes2 cascade; NOTICE: drop cascades to function insertmanyintogisttable13(integer,integer) NOTICE: drop cascades to function insertintogisttable13(integer) NOTICE: drop cascades to function to_box(text) NOTICE: drop cascades to append only table gisttable13 NOTICE: drop cascades to append only table gisttable1 -- end_ignore -- ---------------------------------------------------------------------- -- Test: setup.sql -- ---------------------------------------------------------------------- -- start_ignore create schema qp_gist_indexes2; set search_path to qp_gist_indexes2; -- end_ignore -- ---------------------------------------------------------------------- -- Test: test01create_table.sql -- ---------------------------------------------------------------------- CREATE TABLE GistTable1 ( id INTEGER, owner VARCHAR, description VARCHAR, property BOX, poli POLYGON, bullseye CIRCLE, v VARCHAR, t TEXT, f FLOAT, p POINT, c CIRCLE, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) WITH (APPENDONLY=True, ORIENTATION='column', COMPRESSTYPE=ZLIB, COMPRESSLEVEL=1) DISTRIBUTED BY (id); COPY GistTable1 FROM '@abs_srcdir@/data/PropertyInfo.txt' CSV ; -- ---------------------------------------------------------------------- -- Test: test03IndexScan.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- CREATE INDEX propertyBoxIndex ON GistTable1 USING Gist (property); -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (66, 'Miller', 'Lubbock or leave it', '((3, 1300), (33, 1330))', '( (66,660), (67, 650), (68, 660) )', '( (66, 66), 66)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property ~= '((7052,250),(6050,20))'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= ' ( ( 7052, 250 ) , (6050, 20) )'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; owner | property ---------+---------------------- Hypatia | (7052,250),(6050,20) Patty | (7052,250),(6050,20) (2 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; QUERY PLAN --------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(7052,250),(6050,20)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(7052,250),(6050,20)'::box) Optimizer: PQO version 2.74.0 (6 rows) SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id; id | property ----+---------- 8 | (1 row) -- ---------------------------------------------------------------------- -- Test: test04Insert.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Insert more data. -- ---------------------------------------------------------------------------- -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (76, 'James McMurtry', 'Levelland', '((1500, 1500), (1700, 1900))', '( (76, 77), (76, 75), (75, 77) )', '( (76, 76), 76)' ); -- ---------------------------------------------------------------------- -- Test: test05Select.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 2 | Theodore Turner | a ranch and reserve | (2100,2100),(2000,2000) | ((1,1),(2,1),(1,2)) | <(2,2),2> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Levelland | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | <(76,76),76> 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- ---------------------------------------------------------------------- -- Test: test06IllegalonAO.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; ALTER INDEX propertyBoxIndex RENAME TO propIndex; UPDATE GistTable1 SET description = 'Where''s Johnny?', bullseye = NULL WHERE owner = 'James McMurtry'; -- We should no longer be able to find "Levelland". SELECT property FROM GistTable1 WHERE description = 'Levelland'; property ---------- (0 rows) ALTER INDEX propIndex SET (FILLFACTOR=50); SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) REINDEX INDEX propIndex; SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) -- Delete Theodore Turner DELETE from GistTable1 WHERE property ~= '((2000,2000), (2100, 2100))'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Where's Johnny? | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- Update James McMurtry. UPDATE GistTable1 SET owner = 'Record Company', description = 'fat profit', property = '((100,100), (200, 200))' WHERE property ~= '( (1700,1900),(1500,1500) )'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) ALTER INDEX propIndex RENAME TO propertyBoxIndex; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) DELETE FROM GistTable1 WHERE bullseye = '( (76, 76), 76)'; -- ---------------------------------------------------------------------- -- Test: test07select.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (18 rows) -- ---------------------------------------------------------------------- -- Test: test10MultipleColumns.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test multi-column indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Insert 2 more records, but insert them with the same value for BULLSEYE. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (212, 'Fahrenheit', 'Slightly north of Hades', '( (212, 212), (32, 32) )', '( (212, 212), (600, 600), (70, 70) )', '( (100,100), 212 )' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (100, 'Celsius', 'Barely north of Hades', '( (100, 100), (0, 0) )', '( (100, 100), (600, 600), (70, 70) )', '( (100,100), 212 )' ); -- This should create an index that has duplicate entries for at least one -- value of bullseye. CREATE INDEX i2 ON GistTable1 USING GIST(bullseye); CREATE INDEX i3 ON GistTable1 USING GIST(poli, bullseye); -- This should return 2 rows. SELECT id FROM GistTable1 WHERE bullseye ~= '( (100,100), 212 )'; id ----- 100 212 (2 rows) -- This should return 1 row. SELECT id FROM GistTable1 WHERE property ~= '( (212, 212), (32, 32) )'; id ----- 212 (1 row) -- ---------------------------------------------------------------------- -- Test: test11WherePredicate.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Test GiST indexes with the WHERE predicate in the CREATE INDEX -- statement. This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- -- Add another record that has NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (77, 'S. T. "Rip" Sunset', 'Lost Vegas',NULL, '( (77, 77), (76, 78), (78, 76) )', '( (77, 77), 77)' ); CREATE INDEX propertyIsNullIndex ON GistTable1 USING Gist (property) WHERE property IS NULL; -- Add two more records that have NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (86, 'A. Gent', 'Washingtoon D.C.',NULL, '( (86, 86), (85, 87), (87, 85) )', '( (86, 86), 86)' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (99, 'FelDon Adams', 'Washingtoon D.C.',NULL, '( (99, 99), (97, 98), (98, 97) )', '( (99, 99), 99)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property IS NULL; owner | property -----------------------------+---------- S. T. "Rip" Sunset | A. Gent | Neil, Nell, and Noel Newall | FelDon Adams | (4 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property IS NULL ORDER BY id ; QUERY PLAN ------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property IS NULL) -> Bitmap Index Scan on propertyisnullindex Index Cond: (property IS NULL) Optimizer: Postgres query optimizer (9 rows) -- ---------------------------------------------------------------------- -- Test: test13Vacuum.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test VACUUM on GiST indexes. -- Also test somewhat larger data sets than most of my other GiST index -- tests. -- -- This test suite is for AO (Append-Only) and CO (Column-Oriented) -- tables as well as heap tables, so I removed statement(s) such as -- DELETE that can't be executed on AO and CO tables. -- ---------------------------------------------------------------------------- CREATE TABLE GistTable13 ( id INTEGER, property BOX, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) WITH (APPENDONLY=True, ORIENTATION='column', COMPRESSTYPE=ZLIB, COMPRESSLEVEL=1) DISTRIBUTED BY (id); -- Register a function that converts TEXT to BOX data type. CREATE FUNCTION TO_BOX(TEXT) RETURNS BOX AS $$ SELECT box_in(textout($1)) $$ LANGUAGE SQL; CREATE FUNCTION insertIntoGistTable13 (seed INTEGER) RETURNS VOID AS $$ DECLARE str1 VARCHAR; ss VARCHAR; s2 VARCHAR; BEGIN ss = CAST(seed AS VARCHAR); s2 = CAST((seed - 1) AS VARCHAR); str1 = '((' || ss || ', ' || ss || '), (' || s2 || ', ' || s2 || '))'; INSERT INTO GistTable13(id, property) VALUES (seed, TO_BOX(CAST(str1 AS TEXT)) ); END; $$ LANGUAGE PLPGSQL ; CREATE FUNCTION insertManyIntoGistTable13 (startValue INTEGER, endValue INTEGER) RETURNS VOID AS $$ DECLARE i INTEGER; BEGIN i = startValue; WHILE i <= endValue LOOP PERFORM insertIntoGistTable13(i); i = i + 1; END LOOP; END; $$ LANGUAGE PLPGSQL ; -- Add some rows before we create the index. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) -- Create the index. CREATE INDEX GistIndex13 ON GistTable13 USING GiST (property); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Add more rows after we create the index. SELECT insertManyIntoGistTable13(1001, 2000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values (coordinates), not just the -- AREA, are the same. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) VACUUM GistTable13; ANALYZE GistTable13; SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) TRUNCATE TABLE GistTable13; -- Add some rows. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values are the same, not just the -- same AREA. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) -- ---------------------------------------------------------------------- -- Test: test15ReindexDropIndex.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- REINDEX -- DROP INDEX -- NOTES: -- Although we seemingly ignore the output of the EXPLAIN statements, -- elsewhere in this test we look for "Index Scan on propertyBoxIndex" -- or something similar in order to verify that the index was used. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(3,4),(1,2)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (10 rows) REINDEX INDEX propertyBoxIndex; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(3,4),(1,2)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (10 rows) DROP INDEX propertyBoxIndex; -- Obviously, this shouldn't use the index now that the index is gone. set optimizer_enable_tablescan = TRUE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN -------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Seq Scan on gisttable1 Filter: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (7 rows) -- ---------------------------------------------------------------------- -- Test: teardown.sql -- ---------------------------------------------------------------------- -- start_ignore drop schema qp_gist_indexes2 cascade; NOTICE: drop cascades to function insertmanyintogisttable13(integer,integer) NOTICE: drop cascades to function insertintogisttable13(integer) NOTICE: drop cascades to function to_box(text) NOTICE: drop cascades to append only columnar table gisttable13 NOTICE: drop cascades to append only columnar table gisttable1 -- end_ignore -- ---------------------------------------------------------------------- -- Test: setup.sql -- ---------------------------------------------------------------------- -- start_ignore create schema qp_gist_indexes2; set search_path to qp_gist_indexes2; -- end_ignore -- ---------------------------------------------------------------------- -- Test: test01create_table.sql -- ---------------------------------------------------------------------- CREATE TABLE GistTable1 ( id INTEGER, owner VARCHAR, description VARCHAR, property BOX, poli POLYGON, bullseye CIRCLE, v VARCHAR, t TEXT, f FLOAT, p POINT, c CIRCLE, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) WITH (APPENDONLY=True, ORIENTATION='column') DISTRIBUTED BY (id); COPY GistTable1 FROM '@abs_srcdir@/data/PropertyInfo.txt' CSV ; -- ---------------------------------------------------------------------- -- Test: test03IndexScan.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- CREATE INDEX propertyBoxIndex ON GistTable1 USING Gist (property); -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (66, 'Miller', 'Lubbock or leave it', '((3, 1300), (33, 1330))', '( (66,660), (67, 650), (68, 660) )', '( (66, 66), 66)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property ~= '((7052,250),(6050,20))'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= ' ( ( 7052, 250 ) , (6050, 20) )'; owner | property ---------+---------------------- Patty | (7052,250),(6050,20) Hypatia | (7052,250),(6050,20) (2 rows) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; owner | property ---------+---------------------- Hypatia | (7052,250),(6050,20) Patty | (7052,250),(6050,20) (2 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property ~= '( (6050, 20), (7052, 250) )'; QUERY PLAN --------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(7052,250),(6050,20)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(7052,250),(6050,20)'::box) Optimizer: PQO version 2.74.0 (6 rows) SELECT id, property FROM GistTable1 WHERE property IS NULL ORDER BY id; id | property ----+---------- 8 | (1 row) -- ---------------------------------------------------------------------- -- Test: test04Insert.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Insert more data. -- ---------------------------------------------------------------------------- -- INSERT some more data. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (76, 'James McMurtry', 'Levelland', '((1500, 1500), (1700, 1900))', '( (76, 77), (76, 75), (75, 77) )', '( (76, 76), 76)' ); -- ---------------------------------------------------------------------- -- Test: test05Select.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 2 | Theodore Turner | a ranch and reserve | (2100,2100),(2000,2000) | ((1,1),(2,1),(1,2)) | <(2,2),2> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Levelland | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | <(76,76),76> 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- ---------------------------------------------------------------------- -- Test: test06IllegalonAO.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; ALTER INDEX propertyBoxIndex RENAME TO propIndex; UPDATE GistTable1 SET description = 'Where''s Johnny?', bullseye = NULL WHERE owner = 'James McMurtry'; -- We should no longer be able to find "Levelland". SELECT property FROM GistTable1 WHERE description = 'Levelland'; property ---------- (0 rows) ALTER INDEX propIndex SET (FILLFACTOR=50); SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) REINDEX INDEX propIndex; SELECT owner FROM GistTable1 WHERE property ~= '( (40, 20), (42, 25) )'; owner ---------------------- Hyquotia P. Cucumber (1 row) -- Delete Theodore Turner DELETE from GistTable1 WHERE property ~= '((2000,2000), (2100, 2100))'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | James McMurtry | Where's Johnny? | (1700,1900),(1500,1500) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) -- Update James McMurtry. UPDATE GistTable1 SET owner = 'Record Company', description = 'fat profit', property = '((100,100), (200, 200))' WHERE property ~= '( (1700,1900),(1500,1500) )'; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) ALTER INDEX propIndex RENAME TO propertyBoxIndex; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 8 | Neil, Nell, and Noel Newall | Null Knoll | | | 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (19 rows) DELETE FROM GistTable1 WHERE bullseye = '( (76, 76), 76)'; -- ---------------------------------------------------------------------- -- Test: test07select.sql -- ---------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id, owner, description, property, poli, bullseye FROM GistTable1 WHERE property IS NOT NULL ORDER BY id; id | owner | description | property | poli | bullseye ----+---------------------------------+-----------------------------------------+---------------------------------------------+------------------------------+-------------- 1 | Snidely Whiplash | an empire dreamt of but as yet unbuilt | (1,1),(0,0) | ((0,0),(1,1),(2,2),(3,3)) | <(0,0),0> 3 | Theodore Roosevelt | overlooks Yellowstone River | (1801,2201),(1800,2200) | ((3,3),(3,4),(4,3)) | <(3,3),3> 4 | Donald Duck | overlooks Colorado River | (1001,1001),(1000,1000) | ((4,4),(4,5),(5,4),(5,5)) | <(4,4),4> 5 | Roger Rabbit | Toon Town, unit 127 | (-999999,-100000),(-1000000,-999999) | ((5,-1),(5,-2),(5,-5)) | <(5,5),5> 6 | James T. Kirk | Utopia Planetia | (123456790,123456790),(123456789,123456789) | ((6,7),(6,5),(5,7)) | <(6,6),6> 7 | Albus Dumbledore | Hogwarts | (-11999990,-11999990),(-12000000,-12000000) | ((7,7),(7,8),(6,7)) | <(7,7),7> 9 | Hyquotia P. Cucumber | Radical Vegan Farms | (42,25),(40,20) | ((9,9),(9,8),(8,10)) | <(9,9),9> 10 | Junipero Serra | California | (200,1000),(0,0) | ((10,10),(10,9),(8,11)) | <(10,10),10> 18 | K. Serra | California | (200,1000),(0,0) | ((18,18),(18,19),(17,19)) | <(18,18),18> 19 | Kay Serra | California | (200,1000),(0,0) | ((19,19),(19,18),(20,19)) | <(19,19),19> 38 | Que Sera | California | (200,1000),(0,0) | ((38,38),(38,39),(37,38)) | <(38,38),38> 59 | Hypatia | Greece | (7052,250),(6050,20) | ((59,59),(59,60),(60,59)) | <(59,59),59> 66 | Miller | Lubbock or leave it | (33,1330),(3,1300) | ((66,660),(67,650),(68,660)) | <(66,66),66> 70 | Patty | Cake | (7052,250),(6050,20) | ((70,70),(70,60),(60,70)) | <(70,70),70> 76 | Record Company | fat profit | (200,200),(100,100) | ((76,77),(76,75),(75,77)) | 80 | Leading space man | Leading space woman | (3,4),(1,2) | ((80,80),(80,79),(79,80)) | <(80,80),80> 81 | Trailing space man | trailing space woman | (3,4),(1,2) | ((81,81),(81,80),(80,81)) | <(81,81),81> 82 | Leading and trailing spaces | leading and trailing spaces | (3,4),(1,2) | ((82,82),(82,80),(80,82)) | <(82,82),82> (18 rows) -- ---------------------------------------------------------------------- -- Test: test10MultipleColumns.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test multi-column indexes. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Insert 2 more records, but insert them with the same value for BULLSEYE. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (212, 'Fahrenheit', 'Slightly north of Hades', '( (212, 212), (32, 32) )', '( (212, 212), (600, 600), (70, 70) )', '( (100,100), 212 )' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (100, 'Celsius', 'Barely north of Hades', '( (100, 100), (0, 0) )', '( (100, 100), (600, 600), (70, 70) )', '( (100,100), 212 )' ); -- This should create an index that has duplicate entries for at least one -- value of bullseye. CREATE INDEX i2 ON GistTable1 USING GIST(bullseye); CREATE INDEX i3 ON GistTable1 USING GIST(poli, bullseye); -- This should return 2 rows. SELECT id FROM GistTable1 WHERE bullseye ~= '( (100,100), 212 )'; id ----- 100 212 (2 rows) -- This should return 1 row. SELECT id FROM GistTable1 WHERE property ~= '( (212, 212), (32, 32) )'; id ----- 212 (1 row) -- ---------------------------------------------------------------------- -- Test: test11WherePredicate.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Test GiST indexes with the WHERE predicate in the CREATE INDEX -- statement. This does some simple queries that should use the index. -- We can't see directly whether the index was used, but for each query -- we can run "EXPLAIN" and see whether the query used the index. -- ---------------------------------------------------------------------------- -- Add another record that has NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (77, 'S. T. "Rip" Sunset', 'Lost Vegas',NULL, '( (77, 77), (76, 78), (78, 76) )', '( (77, 77), 77)' ); CREATE INDEX propertyIsNullIndex ON GistTable1 USING Gist (property) WHERE property IS NULL; -- Add two more records that have NULL in the property field. INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (86, 'A. Gent', 'Washingtoon D.C.',NULL, '( (86, 86), (85, 87), (87, 85) )', '( (86, 86), 86)' ); INSERT INTO GistTable1 (id, owner, description, property, poli, bullseye) VALUES (99, 'FelDon Adams', 'Washingtoon D.C.',NULL, '( (99, 99), (97, 98), (98, 97) )', '( (99, 99), 99)' ); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- We should be able to search the column that uses a geometric data type, -- and of course we should find the right rows. We should be able to search -- using different "formats" (e.g. spacing) of the data, and in some cases -- even different "order" of the data (if the data is converted to a -- canonical form, as it is for the BOX data type and perhaps some other -- data types), as long as data in all of those formats should be converted -- to the same internal representation. SELECT owner, property FROM GistTable1 WHERE property IS NULL; owner | property -----------------------------+---------- S. T. "Rip" Sunset | A. Gent | Neil, Nell, and Noel Newall | FelDon Adams | (4 rows) EXPLAIN (COSTS OFF) SELECT owner, property FROM GistTable1 WHERE property IS NULL ORDER BY id ; QUERY PLAN ------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property IS NULL) -> Bitmap Index Scan on propertyisnullindex Index Cond: (property IS NULL) Optimizer: Postgres query optimizer (9 rows) -- ---------------------------------------------------------------------- -- Test: test13Vacuum.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: -- Test VACUUM on GiST indexes. -- Also test somewhat larger data sets than most of my other GiST index -- tests. -- -- This test suite is for AO (Append-Only) and CO (Column-Oriented) -- tables as well as heap tables, so I removed statement(s) such as -- DELETE that can't be executed on AO and CO tables. -- ---------------------------------------------------------------------------- CREATE TABLE GistTable13 ( id INTEGER, property BOX, filler VARCHAR DEFAULT 'This is here just to take up space so that we use more pages of data and sequential scans take a lot more time. Stones tinheads and mixers coming; we did it all on our own; this summer I hear the crunching; 11 dead in Ohio. Got right down to it; we were cutting us down; could have had fun but, no; left them face down dead on the ground. How can you listen when you know?' ) WITH (APPENDONLY=True, ORIENTATION='column') DISTRIBUTED BY (id); -- Register a function that converts TEXT to BOX data type. CREATE FUNCTION TO_BOX(TEXT) RETURNS BOX AS $$ SELECT box_in(textout($1)) $$ LANGUAGE SQL; CREATE FUNCTION insertIntoGistTable13 (seed INTEGER) RETURNS VOID AS $$ DECLARE str1 VARCHAR; ss VARCHAR; s2 VARCHAR; BEGIN ss = CAST(seed AS VARCHAR); s2 = CAST((seed - 1) AS VARCHAR); str1 = '((' || ss || ', ' || ss || '), (' || s2 || ', ' || s2 || '))'; INSERT INTO GistTable13(id, property) VALUES (seed, TO_BOX(CAST(str1 AS TEXT)) ); END; $$ LANGUAGE PLPGSQL ; CREATE FUNCTION insertManyIntoGistTable13 (startValue INTEGER, endValue INTEGER) RETURNS VOID AS $$ DECLARE i INTEGER; BEGIN i = startValue; WHILE i <= endValue LOOP PERFORM insertIntoGistTable13(i); i = i + 1; END LOOP; END; $$ LANGUAGE PLPGSQL ; -- Add some rows before we create the index. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) -- Create the index. CREATE INDEX GistIndex13 ON GistTable13 USING GiST (property); SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; -- Add more rows after we create the index. SELECT insertManyIntoGistTable13(1001, 2000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values (coordinates), not just the -- AREA, are the same. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) VACUUM GistTable13; ANALYZE GistTable13; SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) TRUNCATE TABLE GistTable13; -- Add some rows. SELECT insertManyIntoGistTable13(1, 1000); insertmanyintogisttable13 --------------------------- (1 row) ANALYZE GistTable13; -- Note that "=" for geometric data types means equal AREA, NOT COORDINATES. -- The "~=" operator means that the values are the same, not just the -- same AREA. SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; id | ProperTee -----+--------------------- 999 | (999,999),(998,998) (1 row) EXPLAIN (COSTS OFF) SELECT id, property AS "ProperTee" FROM GistTable13 WHERE property ~= '( (999,999), (998,998) )'; QUERY PLAN -------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Bitmap Heap Scan on gisttable13 Recheck Cond: (property ~= '(999,999),(998,998)'::box) -> Bitmap Index Scan on gistindex13 Index Cond: (property ~= '(999,999),(998,998)'::box) Optimizer: PQO version 2.74.0 (6 rows) -- ---------------------------------------------------------------------- -- Test: test15ReindexDropIndex.sql -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- PURPOSE: Sanity test GiST indexes. -- REINDEX -- DROP INDEX -- NOTES: -- Although we seemingly ignore the output of the EXPLAIN statements, -- elsewhere in this test we look for "Index Scan on propertyBoxIndex" -- or something similar in order to verify that the index was used. -- ---------------------------------------------------------------------------- SET enable_seqscan = FALSE; set optimizer_enable_tablescan = FALSE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ---------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(3,4),(1,2)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (10 rows) REINDEX INDEX propertyBoxIndex; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Result -> Sort Sort Key: id -> Bitmap Heap Scan on gisttable1 Recheck Cond: (property ~= '(3,4),(1,2)'::box) -> Bitmap Index Scan on propertyboxindex Index Cond: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (10 rows) DROP INDEX propertyBoxIndex; -- Obviously, this shouldn't use the index now that the index is gone. set optimizer_enable_tablescan = TRUE; SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; id ---- 80 81 82 (3 rows) EXPLAIN (COSTS OFF) SELECT id FROM GistTable1 WHERE property ~= '( (1,2), (3,4) )' ORDER BY id; QUERY PLAN -------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: id -> Sort Sort Key: id -> Seq Scan on gisttable1 Filter: (property ~= '(3,4),(1,2)'::box) Optimizer: PQO version 2.74.0 (7 rows) -- ---------------------------------------------------------------------- -- Test: teardown.sql -- ---------------------------------------------------------------------- -- start_ignore drop schema qp_gist_indexes2 cascade; NOTICE: drop cascades to function insertmanyintogisttable13(integer,integer) NOTICE: drop cascades to function insertintogisttable13(integer) NOTICE: drop cascades to function to_box(text) NOTICE: drop cascades to append only columnar table gisttable13 NOTICE: drop cascades to append only columnar table gisttable1 -- end_ignore