Database postgres index vaccum learning
VoicePortal=# create table testindex (no serial primary key, value integer)
NOTICE: CREATE TABLE will create implicit sequence "testindex_no_seq" for serial column "testindex.no"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "testindex_pkey" for table "testindex"
VoicePortal=# insert into testindex (value)
VoicePortal-# select trunc (random () * 10) from generate_series (1mem1000); random () produces a number of 0.1 *, where 10 is a number less than ten.
INSERT 0 1000 generates a thousand random numbers up to 10 and inserts them into testindex as value values
VoicePortal=# select * from testindex limit 10
No | value
-- +-
| 1 | 0 |
2 | 8
3 | 3
4 | 2
5 | 8
6 | 9
7 | 6
8 | 5
9 | 9
10 | 9
(10 rows)
So testindex's table is built and you can use it for testing.
VoicePortal=#\ d testindex
Table "public.testindex"
Column | Type | Modifiers
-+-
No | integer | not null default nextval ('testindex_no_seq'::regclass)
Value | integer |
Indexes:
"testindex_pkey" PRIMARY KEY, btree (no) automatically index
Flow out details of a table.
VoicePortal=#\ di+ testindex_pkey look up the index size of the table
List of relations
Schema | Name | Type | Owner | Table | Size | Description
-+-
Public | testindex_pkey | index | postgres | testindex | 40 kB |
(1 row)
VoicePortal=#\ dt+ testindex look up the table size
List of relations
Schema | Name | Type | Owner | Size | Description
-+-
Public | testindex | table | postgres | 64 kB |
(1 row)
Now that there are 1000 pieces of data in testindex, I'll add another 1000 to it.
VoicePortal=# insert into testindex VALUES ('1001)
INSERT 0 1
VoicePortal=# select count (*) from testindex
Count
1001
(1 row)
VoicePortal=# insert into testindex (value)
Select trunc (random () * 10) from generate_series (1002Jing 1100)
INSERT 0 99
VoicePortal=# insert into testindex (value)
VoicePortal-# select trunc (random () * 10) from generate_series (1101 ~ 2000)
INSERT 0 900
VoicePortal=#\ di+ testindex_pkey
List of relations
-[RECORD 1]-
Schema | public
Name | testindex_pkey
Type | index
Owner | postgres
Table | testindex
Size | 64 kB
Description |
VoicePortal=#\ dt+ testindex
List of relations
-[RECORD 1]-
Schema | public
Name | testindex
Type | table
Owner | postgres
Size | 96 kB
Description |
Delete some more data
VoicePortal=# delete FROM testindex where value
