create_index.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 494 行 · 第 1/2 页

   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(11 rows)

SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
 seqno |       i       |                                                             t                                                              
-------+---------------+----------------------------------------------------------------------------------------------------------------------------
    40 | {34}          | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
    74 | {32}          | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
(3 rows)

SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
 seqno |    i    |                                                        t                                                        
-------+---------+-----------------------------------------------------------------------------------------------------------------
    95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
(1 row)

CREATE INDEX textarrayidx ON array_index_op_test USING gin (t);
SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
 seqno |           i           |                                                                     t                                                                      
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
    22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
    45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
    72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
    79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(4 rows)

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
 seqno |           i           |                                                                     t                                                                      
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
    22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
    45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
    72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
    79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(4 rows)

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
 seqno |        i         |                                 t                                  
-------+------------------+--------------------------------------------------------------------
    15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
    79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
    96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
(3 rows)

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
 seqno |        i         |                                 t                                  
-------+------------------+--------------------------------------------------------------------
    15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
    79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
    96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
(3 rows)

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 seqno |  i   |                                 t                                  
-------+------+--------------------------------------------------------------------
    79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(1 row)

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 seqno |           i           |                                                                     t                                                                      
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
    15 | {17,14,16,63,67}      | {AA6416,AAAAAAAAAA646,AAAAA95309}
    22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
    45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
    72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
    79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
    96 | {23,97,43}            | {AAAAAAAAAA646,A87088}
(6 rows)

SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
 seqno |         i          |                                                     t                                                     
-------+--------------------+-----------------------------------------------------------------------------------------------------------
    22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
    45 | {99,45}            | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
(2 rows)

SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno;
 seqno |     i      |           t            
-------+------------+------------------------
    96 | {23,97,43} | {AAAAAAAAAA646,A87088}
(1 row)

RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
--
-- HASH
--
CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
--
-- Test functional index
--
CREATE TABLE func_index_heap (f1 text, f2 text);
CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
INSERT INTO func_index_heap VALUES('ABC','DEF');
INSERT INTO func_index_heap VALUES('AB','CDEFG');
INSERT INTO func_index_heap VALUES('QWE','RTY');
-- this should fail because of unique index:
INSERT INTO func_index_heap VALUES('ABCD', 'EF');
ERROR:  duplicate key value violates unique constraint "func_index_index"
-- but this shouldn't:
INSERT INTO func_index_heap VALUES('QWERTY');
--
-- Same test, expressional index
--
DROP TABLE func_index_heap;
CREATE TABLE func_index_heap (f1 text, f2 text);
CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
INSERT INTO func_index_heap VALUES('ABC','DEF');
INSERT INTO func_index_heap VALUES('AB','CDEFG');
INSERT INTO func_index_heap VALUES('QWE','RTY');
-- this should fail because of unique index:
INSERT INTO func_index_heap VALUES('ABCD', 'EF');
ERROR:  duplicate key value violates unique constraint "func_index_index"
-- but this shouldn't:
INSERT INTO func_index_heap VALUES('QWERTY');
--
-- Also try building functional, expressional, and partial indexes on
-- tables that already contain data.
--
create unique index hash_f8_index_1 on hash_f8_heap(abs(random));
create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);
create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
--
-- Try some concurrent index builds
--
-- Unfortunately this only tests about half the code paths because there are
-- no concurrent updates happening to the table at the same time.
CREATE TABLE concur_heap (f1 text, f2 text);
-- empty table
CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
INSERT INTO concur_heap VALUES  ('a','b');
INSERT INTO concur_heap VALUES  ('b','b');
-- unique index
CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
-- check if constraint is set up properly to be enforced
INSERT INTO concur_heap VALUES ('b','x');
ERROR:  duplicate key value violates unique constraint "concur_index2"
-- check if constraint is enforced properly at build time
CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);
ERROR:  could not create unique index "concur_index3"
DETAIL:  Table contains duplicated values.
-- test that expression indexes and partial indexes work concurrently
CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';
CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';
CREATE INDEX CONCURRENTLY concur_index6 on concur_heap((f2||f1));
-- You can't do a concurrent index build in a transaction
BEGIN;
CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
COMMIT;
-- But you can do a regular index build in a transaction
BEGIN;
CREATE INDEX std_index on concur_heap(f2);
COMMIT;
-- check to make sure that the failed indexes were cleaned up properly and the
-- successful indexes are created properly. Notably that they do NOT have the
-- "invalid" flag set.
\d concur_heap
Table "public.concur_heap"
 Column | Type | Modifiers 
--------+------+-----------
 f1     | text | 
 f2     | text | 
Indexes:
    "concur_index2" UNIQUE, btree (f1)
    "concur_index3" UNIQUE, btree (f2) INVALID
    "concur_index1" btree (f2, f1)
    "concur_index4" btree (f2) WHERE f1 = 'a'::text
    "concur_index5" btree (f2) WHERE f1 = 'x'::text
    "concur_index6" btree ((f2 || f1))
    "std_index" btree (f2)

DROP TABLE concur_heap;
--
-- Tests for IS NULL with b-tree indexes
--
SELECT unique1, unique2 INTO onek_with_null FROM onek;
INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 count 
-------
     2
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 count 
-------
     1
(1 row)

DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 count 
-------
     2
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 count 
-------
     1
(1 row)

DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 count 
-------
     2
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 count 
-------
     1
(1 row)

DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2  nulls first,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 count 
-------
     2
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 count 
-------
     1
(1 row)

RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
 
DROP TABLE onek_with_null;