Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / qp_gist_indexes2_optimizer.source
blob: 902c3c7d5e28f5bd92f61bfd7546f115361c2a6e [file] [log] [blame]
-- ----------------------------------------------------------------------
-- Test: setup.sql
-- ----------------------------------------------------------------------
create schema qp_gist_indexes2;
set search_path to qp_gist_indexes2;
-- start_ignore
create language plpython3u;
-- 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 plpython3u;
-- ----------------------------------------------------------------------
-- 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
;
ANALYZE GistTable1;
-- ----------------------------------------------------------------------
-- 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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 Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
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 Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
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 Postgres-based 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 Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
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 Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
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 Postgres-based 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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
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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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)
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: Pivotal Optimizer (GPORCA) 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
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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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, 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: Pivotal Optimizer (GPORCA) 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
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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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, 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: Pivotal Optimizer (GPORCA) 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
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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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: Pivotal Optimizer (GPORCA) 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