DB2020FALL/week10.sql at master · bobur554396/DB2020FALL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

CREATE TABLE test (
  id      INTEGER,
  content VARCHAR(255)
);

SELECT *
FROM test;

CREATE INDEX test_id_index
  ON test (id);
DROP INDEX test_id_index;

CREATE INDEX ttt
  ON test USING HASH (id);
DROP INDEX ttt;

SELECT *
FROM test
WHERE id = 123;

ANALYSE;

SELECT *
FROM pg_statistic;

SELECT *
FROM pg_indexes;

CREATE UNIQUE INDEX ttt
  ON test (id);

SELECT *
FROM test
WHERE lower(content) = 'content 1';


CREATE INDEX test_content_index
  ON test (lower(content));
DROP INDEX ttt;
DROP INDEX test_id_index;

SELECT count(*)
FROM test;

TRUNCATE test;


INSERT INTO test (id, content)
  SELECT
    i,
    'content_' || i
  FROM
        generate_series(1, 1000000) i;

SELECT count(*)
FROM test;

TRUNCATE test;

EXPLAIN ANALYSE SELECT *
                FROM test
                WHERE id = 436123;

CREATE INDEX test_id_index
  ON test (id);

ANALYZE;

SELECT *
FROM pg_statistic;

EXPLAIN ANALYSE SELECT *
                FROM test
                WHERE id = 4;


EXPLAIN ANALYSE SELECT *
                FROM test
                WHERE lower(content) = 'content_30';


CREATE INDEX test_content_index ON test(lower(content));
ANALYSE;


SELECT *
FROM pg_indexes;