The use of indexes can enhance database performance by enabling the database server to find rows faster. You can create, drop, and list indexes, as well as use indexes on expressions.
Once you’ve created an index, you might forget the exact syntax on how you created it. No problem. You can use the PG_GET_INDEXDEF function to grab the DDL for any index!
Example:
yugabyte=# CREATE TABLE countries (country_pk INT PRIMARY KEY, country_region VARCHAR(10), country_code VARCHAR(10), country_name VARCHAR(200));
CREATE TABLE
yugabyte=# CREATE INDEX countries_county_code_name_eu ON countries(country_code) INCLUDE (country_name) WHERE country_region = 'EU';
CREATE INDEX
yugabyte=# \d countries
Table "public.countries"
Column | Type | Collation | Nullable | Default
----------------+------------------------+-----------+----------+---------
country_pk | integer | | not null |
country_region | character varying(10) | | |
country_code | character varying(10) | | |
country_name | character varying(200) | | |
Indexes:
"countries_pkey" PRIMARY KEY, lsm (country_pk HASH)
"countries_county_code_name_eu" lsm (country_code HASH) INCLUDE (country_name) WHERE country_region::text = 'EU'::text
yugabyte=# SELECT pg_get_indexdef('countries_county_code_name_eu'::regclass);
pg_get_indexdef
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE INDEX countries_county_code_name_eu ON public.countries USING lsm (country_code HASH) INCLUDE (country_name) WHERE ((country_region)::text = 'EU'::text)
(1 row)
Have Fun!