Index in Postgresql
INDEX
user for increase the performance.
create index to a table is avoiding the fullscan when u retreive the row using the index based column.
postgreSQL provides index types
B-tree
Hash
GiST
GIN
www.sakthidbtech.com=> \d t1;
Table "dinesh.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
t_id | numeric(5,0) |
name | character varying(10) |
city | text |
Tablespace: "tbsone"
B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:
<
<=
=
>=
>
Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. (But note that IS NULL is not equivalent to = and is not indexable.)
The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your server does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries. See Section 11.8 below. It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e. characters that are not affected by upper/lower case conversion.
--to create B-tree index
sdbt=> create unique index t1_id1 on t1(t_id);
CREATE INDEX
www.sakthidbtech.com=> \d t1;
Table "dinesh.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
t_id | numeric(5,0) |
name | character varying(10) |
city | text |
Indexes:
"t1_id1" UNIQUE, btree (t_id)
Tablespace: "tbsone"
Hash indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the = operator. The following command is used to create a hash index:
create index ind_new on emp using hash(ename);
Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. Furthermore, hash index operations are not presently WAL-logged, so hash indexes may need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged
--collation for index uses
www.sakthidbtech.com=> create collation fr_FR (
www.sakthidbtech.com(> locale = 'fr_FR.utf8');
CREATE COLLATION
www.sakthidbtech.com=> create table test_d(
www.sakthidbtech.com(> french_name varchar2(10) collate "fr_FR",
www.sakthidbtech.com(> eng_name varchar2(10) collate "en_US");
CREATE TABLE
www.sakthidbtech.com=> insert into test_d values('le','la');
INSERT 0 1
www.sakthidbtech.com=> insert into test_d values('la','le');
INSERT 0 1
www.sakthidbtech.com=> commit;
COMMIT
www.sakthidbtech.com=> select * from test_d;
french_name | eng_name
-------------+----------
le | la
la | le
(2 rows)
www.sakthidbtech.com=> \d test_d
Table "dinesh.test_d"
Column | Type | Modifiers
-------------+-----------------------+---------------
french_name | character varying(10) | collate fr_FR
eng_name | character varying(10) | collate en_US
www.sakthidbtech.com=> create index test_d_id1 on test_d(french_name collate "fr_FR");
CREATE INDEX
www.sakthidbtech.com=> \d test_d
Table "dinesh.test_d"
Column | Type | Modifiers
-------------+-----------------------+---------------
french_name | character varying(10) | collate fr_FR
eng_name | character varying(10) | collate en_US
Indexes:
"test_d_id1" btree (french_name)
www.sakthidbtech.com=> create index test_d_id2 on test_d (eng_name collate "de_DE");
CREATE INDEX
www.sakthidbtech.com=> \d test_d
Table "dinesh.test_d"
Column | Type | Modifiers
-------------+-----------------------+---------------
french_name | character varying(10) | collate fr_FR
eng_name | character varying(10) | collate en_US
Indexes:
"test_d_id1" btree (french_name)
"test_d_id2" btree (eng_name COLLATE "de_DE")
www.sakthidbtech.com=> create index t2_id1 on t2(sid) tablespace tbsone;
CREATE INDEX
www.sakthidbtech.com=> select * from pg_indexes where tablename='t2';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+---------------------------------------------
dinesh | t2 | t2_id1 | tbsone | CREATE INDEX t2_id1 ON t2 USING btree (sid)
(1 row)
www.sakthidbtech.com=> select index_name,index_type, table_name, tablespace_name, status
www.sakthidbtech.com-> from user_indexes;
index_name | index_type | table_name | tablespace_name | status
--------------+------------+------------+-----------------+--------
T1_ID1 | BTREE | T1 | | VALID
T1_LOWER_IDX | BTREE | T1 | | VALID
T1_ID2 | BTREE | T1 | | VALID
TEST_D_ID1 | BTREE | TEST_D | | VALID
TEST_D_ID2 | BTREE | TEST_D | | VALID
T2_ID1 | BTREE | T2 | TBSONE | VALID
(6 rows)
www.sakthidbtech.com=>
GIN INDEX
GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example. Like GiST, GIN can support many different user-defined indexing strategies and the particular operators with which a GIN index can be used vary depending on the indexing strategy. As an example, the standard distribution of PostgreSQL includes GIN operator classes for one-dimensional arrays, which support indexed queries using these operators:
<@
@>
=
&&
Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type
www.sakthidbtech.com=> create table land
www.sakthidbtech.com-> (id int[]);
CREATE TABLE
www.sakthidbtech.com=> insert into "land" values ('{10, 20, 30}');
INSERT 0 1
www.sakthidbtech.com=> insert into "land" values ('{20, 30, 40}');
INSERT 0 1
www.sakthidbtech.com=> create index land_idx1 on land using gin("id");
CREATE INDEX
www.sakthidbtech.com=> \d land
Table "dinesh.land"
Column | Type | Modifiers
--------+-----------+-----------
id | integer[] |
Indexes:
"land_idx1" gin (id)
www.sakthidbtech.com=> select * from land;
id
------------
{10,20,30}
{20,30,40}
(2 rows)
www.sakthidbtech.com=> analyze land;
ANALYZE
www.sakthidbtech.com=> explain analyze
www.sakthidbtech.com-> select * from land where id @>ARRAY[20];
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on land (cost=0.00..1.02 rows=1 width=33) (actual time=0.030..0.036 rows=2 loops=1)
Filter: (id @> '{20}'::integer[])
Total runtime: 0.089 ms
(3 rows)
14 down vote favorite
1 From what information I could find, they both solve the same problems - more esoteric operations like array containment and intersection (&&, @>, <@, etc). However I would be interested in advice about when to use one or the other (or neither possibly). The PostgreSQL documentation has some information about this:
• GIN index lookups are about three times faster than GiST
• GIN indexes take about three times longer to build than GiST
• GIN indexes are about ten times slower to update than GiST
• GIN indexes are two-to-three times larger than GiST
GiST index
GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operators with which a GiST index can be used vary depending on the indexing strategy (the operator class). As an example, the standard distribution of PostgreSQL includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators:
<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&
Many other GiST operator classes are available in the contrib collection or as separate projects.
www.sakthidbtech.com=> create table gist_text(id box);
CREATE TABLE
www.sakthidbtech.com=> create index gist_idx on gist_text using gist(id);
CREATE INDEX
www.sakthidbtech.com=# \d gist_text
Table "dinesh.gist_text"
Column | Type | Modifiers
--------+------+-----------
id | box |
Indexes:
"gist_idx" gist (id)
www.sakthidbtech.com=> insert into gist_text values ('(8,9),(1,3)');
INSERT 0 1
www.sakthidbtech.com=> select * from gist_text;
id
-------------
(8,9),(1,3)
(1 row)
www.sakthidbtech.com=> create table text_g( id number(10),
www.sakthidbtech.com(> name varchar2(10),data circle);
CREATE TABLE
www.sakthidbtech.com=>
www.sakthidbtech.com=> create index gist_idx2
www.sakthidbtech.com-> on text_g using gist(data);
CREATE INDEX
www.sakthidbtech.com=> \d text_g
Table "dinesh.text_g"
Column | Type | Modifiers
--------+-----------------------+-----------
id | numeric(10,0) |
name | character varying(10) |
data | circle |
Indexes:
"gist_idx2" gist (data)
www.sakthidbtech.com=> update text_g set data='10,4,10';
UPDATE 100
www.sakthidbtech.com=> select count(*) from text_g
www.sakthidbtech.com-> ;
count
-------
100
(1 row)
www.sakthidbtech.com=> select * from text_g where id<10;
id | name | data
----+------+-------------
1 | sss | <(10,4),10>
2 | sss | <(10,4),10>
3 | sss | <(10,4),10>
4 | sss | <(10,4),10>
5 | sss | <(10,4),10>
6 | sss | <(10,4),10>
7 | sss | <(10,4),10>
8 | sss | <(10,4),10>
9 | sss | <(10,4),10>
(9 rows)
www.sakthidbtech.com=> create index s_idx on stud using hash(s_name);
CREATE INDEX
www.sakthidbtech.com=> \d stud
Table "dinesh.stud"
Column | Type | Modifiers
---------+-----------------------+-----------
s_id | numeric(8,0) |
s_name | character varying(10) |
s_dno | numeric(5,0) |
s_phone | numeric(10,0) |
Indexes:
"s_idx" hash (s_name)
Foreign-key constraints:
"stud_s_dno_fkey" FOREIGN KEY (s_dno) REFERENCES dept(d_no)
www.sakthidbtech.com=> drop index t1_id1;
DROP INDEX
multicolumn indexes(composite index):
create index ind on emp(emp_id, d_id);
A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they'd still have to be scanned through. This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.
unique index
create unique index ind1 on emp (empid,deptid);
An index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.
PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table. The index covers the columns that make up the primary key or unique columns (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.
Indexes on expressions:
www.sakthidbtech.com=> create index on t1 (lower(name));
CREATE INDEX
www.sakthidbtech.com=> \d t1;
Table "dinesh.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
t_id | numeric(5,0) |
name | character varying(10) |
city | text |
Indexes:
"t1_id1" UNIQUE, btree (t_id)
"t1_lower_idx" btree (lower(name::text))
Tablespace: "tbsone"
--to create a function based index
www.sakthidbtech.com=> create index t1_id2 on t1(upper(city));
CREATE INDEX
www.sakthidbtech.com=> select * from t1 where upper(city)='CHENNAI';
www.sakthidbtech.com=> \d t1;
Table "dinesh.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
t_id | numeric(5,0) |
name | character varying(10) |
city | text |
Indexes:
"t1_id1" UNIQUE, btree (t_id)
"t1_id2" btree (upper(city))
"t1_lower_idx" btree (lower(name::text))
Tablespace: "tbsone"
create index ind on emp ((name||' '||fathersname));
select * from emp where ((name||' '||fathersname))='JACK McHALS';
EXAMINE INDEX USAGE
PostgreSQL do not need maintenance and tuning, it is still important to check which indexes are actually used by the real-life query workload. Examining index usage for an individual query is done with the EXPLAIN command;
www.sakthidbtech.com=> explain analyze select * from land where id @>ARRAY[20];
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on land (cost=0.00..1.02 rows=1 width=33) (actual time=0.030..0.036 rows=2 loops=1)
Filter: (id @> '{20}'::integer[])
Total runtime: 0.089 ms
(3 rows)
user for increase the performance.
create index to a table is avoiding the fullscan when u retreive the row using the index based column.
postgreSQL provides index types
B-tree
Hash
GiST
GIN
www.sakthidbtech.com=> \d t1;
Table "dinesh.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
t_id | numeric(5,0) |
name | character varying(10) |
city | text |
Tablespace: "tbsone"
B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:
<
<=
=
>=
>
Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. (But note that IS NULL is not equivalent to = and is not indexable.)
The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your server does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries. See Section 11.8 below. It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e. characters that are not affected by upper/lower case conversion.
--to create B-tree index
sdbt=> create unique index t1_id1 on t1(t_id);
CREATE INDEX
www.sakthidbtech.com=> \d t1;
Table "dinesh.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
t_id | numeric(5,0) |
name | character varying(10) |
city | text |
Indexes:
"t1_id1" UNIQUE, btree (t_id)
Tablespace: "tbsone"
Hash indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the = operator. The following command is used to create a hash index:
create index ind_new on emp using hash(ename);
Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. Furthermore, hash index operations are not presently WAL-logged, so hash indexes may need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged
--collation for index uses
www.sakthidbtech.com=> create collation fr_FR (
www.sakthidbtech.com(> locale = 'fr_FR.utf8');
CREATE COLLATION
www.sakthidbtech.com=> create table test_d(
www.sakthidbtech.com(> french_name varchar2(10) collate "fr_FR",
www.sakthidbtech.com(> eng_name varchar2(10) collate "en_US");
CREATE TABLE
www.sakthidbtech.com=> insert into test_d values('le','la');
INSERT 0 1
www.sakthidbtech.com=> insert into test_d values('la','le');
INSERT 0 1
www.sakthidbtech.com=> commit;
COMMIT
www.sakthidbtech.com=> select * from test_d;
french_name | eng_name
-------------+----------
le | la
la | le
(2 rows)
www.sakthidbtech.com=> \d test_d
Table "dinesh.test_d"
Column | Type | Modifiers
-------------+-----------------------+---------------
french_name | character varying(10) | collate fr_FR
eng_name | character varying(10) | collate en_US
www.sakthidbtech.com=> create index test_d_id1 on test_d(french_name collate "fr_FR");
CREATE INDEX
www.sakthidbtech.com=> \d test_d
Table "dinesh.test_d"
Column | Type | Modifiers
-------------+-----------------------+---------------
french_name | character varying(10) | collate fr_FR
eng_name | character varying(10) | collate en_US
Indexes:
"test_d_id1" btree (french_name)
www.sakthidbtech.com=> create index test_d_id2 on test_d (eng_name collate "de_DE");
CREATE INDEX
www.sakthidbtech.com=> \d test_d
Table "dinesh.test_d"
Column | Type | Modifiers
-------------+-----------------------+---------------
french_name | character varying(10) | collate fr_FR
eng_name | character varying(10) | collate en_US
Indexes:
"test_d_id1" btree (french_name)
"test_d_id2" btree (eng_name COLLATE "de_DE")
www.sakthidbtech.com=> create index t2_id1 on t2(sid) tablespace tbsone;
CREATE INDEX
www.sakthidbtech.com=> select * from pg_indexes where tablename='t2';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+---------------------------------------------
dinesh | t2 | t2_id1 | tbsone | CREATE INDEX t2_id1 ON t2 USING btree (sid)
(1 row)
www.sakthidbtech.com=> select index_name,index_type, table_name, tablespace_name, status
www.sakthidbtech.com-> from user_indexes;
index_name | index_type | table_name | tablespace_name | status
--------------+------------+------------+-----------------+--------
T1_ID1 | BTREE | T1 | | VALID
T1_LOWER_IDX | BTREE | T1 | | VALID
T1_ID2 | BTREE | T1 | | VALID
TEST_D_ID1 | BTREE | TEST_D | | VALID
TEST_D_ID2 | BTREE | TEST_D | | VALID
T2_ID1 | BTREE | T2 | TBSONE | VALID
(6 rows)
www.sakthidbtech.com=>
GIN INDEX
GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example. Like GiST, GIN can support many different user-defined indexing strategies and the particular operators with which a GIN index can be used vary depending on the indexing strategy. As an example, the standard distribution of PostgreSQL includes GIN operator classes for one-dimensional arrays, which support indexed queries using these operators:
<@
@>
=
&&
Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type
www.sakthidbtech.com=> create table land
www.sakthidbtech.com-> (id int[]);
CREATE TABLE
www.sakthidbtech.com=> insert into "land" values ('{10, 20, 30}');
INSERT 0 1
www.sakthidbtech.com=> insert into "land" values ('{20, 30, 40}');
INSERT 0 1
www.sakthidbtech.com=> create index land_idx1 on land using gin("id");
CREATE INDEX
www.sakthidbtech.com=> \d land
Table "dinesh.land"
Column | Type | Modifiers
--------+-----------+-----------
id | integer[] |
Indexes:
"land_idx1" gin (id)
www.sakthidbtech.com=> select * from land;
id
------------
{10,20,30}
{20,30,40}
(2 rows)
www.sakthidbtech.com=> analyze land;
ANALYZE
www.sakthidbtech.com=> explain analyze
www.sakthidbtech.com-> select * from land where id @>ARRAY[20];
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on land (cost=0.00..1.02 rows=1 width=33) (actual time=0.030..0.036 rows=2 loops=1)
Filter: (id @> '{20}'::integer[])
Total runtime: 0.089 ms
(3 rows)
14 down vote favorite
1 From what information I could find, they both solve the same problems - more esoteric operations like array containment and intersection (&&, @>, <@, etc). However I would be interested in advice about when to use one or the other (or neither possibly). The PostgreSQL documentation has some information about this:
• GIN index lookups are about three times faster than GiST
• GIN indexes take about three times longer to build than GiST
• GIN indexes are about ten times slower to update than GiST
• GIN indexes are two-to-three times larger than GiST
GiST index
GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operators with which a GiST index can be used vary depending on the indexing strategy (the operator class). As an example, the standard distribution of PostgreSQL includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators:
<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&
Many other GiST operator classes are available in the contrib collection or as separate projects.
www.sakthidbtech.com=> create table gist_text(id box);
CREATE TABLE
www.sakthidbtech.com=> create index gist_idx on gist_text using gist(id);
CREATE INDEX
www.sakthidbtech.com=# \d gist_text
Table "dinesh.gist_text"
Column | Type | Modifiers
--------+------+-----------
id | box |
Indexes:
"gist_idx" gist (id)
www.sakthidbtech.com=> insert into gist_text values ('(8,9),(1,3)');
INSERT 0 1
www.sakthidbtech.com=> select * from gist_text;
id
-------------
(8,9),(1,3)
(1 row)
www.sakthidbtech.com=> create table text_g( id number(10),
www.sakthidbtech.com(> name varchar2(10),data circle);
CREATE TABLE
www.sakthidbtech.com=>
www.sakthidbtech.com=> create index gist_idx2
www.sakthidbtech.com-> on text_g using gist(data);
CREATE INDEX
www.sakthidbtech.com=> \d text_g
Table "dinesh.text_g"
Column | Type | Modifiers
--------+-----------------------+-----------
id | numeric(10,0) |
name | character varying(10) |
data | circle |
Indexes:
"gist_idx2" gist (data)
www.sakthidbtech.com=> update text_g set data='10,4,10';
UPDATE 100
www.sakthidbtech.com=> select count(*) from text_g
www.sakthidbtech.com-> ;
count
-------
100
(1 row)
www.sakthidbtech.com=> select * from text_g where id<10;
id | name | data
----+------+-------------
1 | sss | <(10,4),10>
2 | sss | <(10,4),10>
3 | sss | <(10,4),10>
4 | sss | <(10,4),10>
5 | sss | <(10,4),10>
6 | sss | <(10,4),10>
7 | sss | <(10,4),10>
8 | sss | <(10,4),10>
9 | sss | <(10,4),10>
(9 rows)
www.sakthidbtech.com=> create index s_idx on stud using hash(s_name);
CREATE INDEX
www.sakthidbtech.com=> \d stud
Table "dinesh.stud"
Column | Type | Modifiers
---------+-----------------------+-----------
s_id | numeric(8,0) |
s_name | character varying(10) |
s_dno | numeric(5,0) |
s_phone | numeric(10,0) |
Indexes:
"s_idx" hash (s_name)
Foreign-key constraints:
"stud_s_dno_fkey" FOREIGN KEY (s_dno) REFERENCES dept(d_no)
www.sakthidbtech.com=> drop index t1_id1;
DROP INDEX
multicolumn indexes(composite index):
create index ind on emp(emp_id, d_id);
A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they'd still have to be scanned through. This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.
unique index
create unique index ind1 on emp (empid,deptid);
An index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.
PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table. The index covers the columns that make up the primary key or unique columns (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.
Indexes on expressions:
www.sakthidbtech.com=> create index on t1 (lower(name));
CREATE INDEX
www.sakthidbtech.com=> \d t1;
Table "dinesh.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
t_id | numeric(5,0) |
name | character varying(10) |
city | text |
Indexes:
"t1_id1" UNIQUE, btree (t_id)
"t1_lower_idx" btree (lower(name::text))
Tablespace: "tbsone"
--to create a function based index
www.sakthidbtech.com=> create index t1_id2 on t1(upper(city));
CREATE INDEX
www.sakthidbtech.com=> select * from t1 where upper(city)='CHENNAI';
www.sakthidbtech.com=> \d t1;
Table "dinesh.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
t_id | numeric(5,0) |
name | character varying(10) |
city | text |
Indexes:
"t1_id1" UNIQUE, btree (t_id)
"t1_id2" btree (upper(city))
"t1_lower_idx" btree (lower(name::text))
Tablespace: "tbsone"
create index ind on emp ((name||' '||fathersname));
select * from emp where ((name||' '||fathersname))='JACK McHALS';
EXAMINE INDEX USAGE
PostgreSQL do not need maintenance and tuning, it is still important to check which indexes are actually used by the real-life query workload. Examining index usage for an individual query is done with the EXPLAIN command;
www.sakthidbtech.com=> explain analyze select * from land where id @>ARRAY[20];
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on land (cost=0.00..1.02 rows=1 width=33) (actual time=0.030..0.036 rows=2 loops=1)
Filter: (id @> '{20}'::integer[])
Total runtime: 0.089 ms
(3 rows)
Comments
Post a Comment