インデックス構成項目
インデックス名とcreate index文を表示します。Oracleと異なりSELECT文で簡単にDDLを出力する事ができます。
select --t.schemaname,t.tablename, t.indexname,t.indexdef from pg_indexes t where t.schemaname = 'sooni' order by t.tablename ;
インデックス構成項目出力例
indexname | indexdef
--------------------------------+-------------------------------------------------------------------------------------------------------------------
addres_list_idx02 | CREATE INDEX addres_list_idx02 ON ONLY sooni.address_list USING btree (city)
addres_list_idx01 | CREATE INDEX addres_list_idx01 ON ONLY sooni.address_list USING btree (prefectures, city)
aichi_prefectures_city_idx | CREATE INDEX aichi_prefectures_city_idx ON sooni.aichi USING btree (prefectures, city)
aichi_city_idx | CREATE INDEX aichi_city_idx ON sooni.aichi USING btree (city)
cable_pkey | CREATE UNIQUE INDEX cable_pkey ON sooni.cable USING btree (id, test_item)
chiba_city_idx | CREATE INDEX chiba_city_idx ON sooni.chiba USING btree (city)
chiba_prefectures_city_idx | CREATE INDEX chiba_prefectures_city_idx ON sooni.chiba USING btree (prefectures, city)
idx01_ex400 | CREATE INDEX idx01_ex400 ON sooni.ex400 USING btree (employee_number)
fukushima_city_idx | CREATE INDEX fukushima_city_idx ON sooni.fukushima USING btree (city)
fukushima_prefectures_city_idx | CREATE INDEX fukushima_prefectures_city_idx ON sooni.fukushima USING btree (prefectures, city)
gunma_prefectures_city_idx | CREATE INDEX gunma_prefectures_city_idx ON sooni.gunma USING btree (prefectures, city)
gunma_city_idx | CREATE INDEX gunma_city_idx ON sooni.gunma USING btree (city)
hiroshima_city_idx | CREATE INDEX hiroshima_city_idx ON sooni.hiroshima USING btree (city)
hiroshima_prefectures_city_idx | CREATE INDEX hiroshima_prefectures_city_idx ON sooni.hiroshima USING btree (prefectures, city)
hoge_pkey | CREATE UNIQUE INDEX hoge_pkey ON sooni.hoge USING btree (id)
hokkaido_prefectures_city_idx | CREATE INDEX hokkaido_prefectures_city_idx ON sooni.hokkaido USING btree (prefectures, city)
hokkaido_city_idx | CREATE INDEX hokkaido_city_idx ON sooni.hokkaido USING btree (city)
hyogo_city_idx | CREATE INDEX hyogo_city_idx ON sooni.hyogo USING btree (city)
hyogo_prefectures_city_idx | CREATE INDEX hyogo_prefectures_city_idx ON sooni.hyogo USING btree (prefectures, city)
uni_idx_kokudo_address_chome | CREATE UNIQUE INDEX uni_idx_kokudo_address_chome ON sooni.kokudo_address_chome USING btree (oaza_town_chome_code)インデックス構成項目
select n.nspname as table_schema
,ct.relname as table_name
,ci.relname as index_name
,case when i.indisunique then 'unique' else '' end uniquenes
,case when i.indisprimary then 'pk' else '' end primarykey
,array_to_string( array_agg( at.attname ), ',') AS column_names
from pg_index i
inner join pg_class ci
on i.indexrelid = ci.oid
inner join pg_attribute AS at
on at.attrelid = ci.oid
inner join pg_class ct
on i.indrelid = ct.oid
inner join pg_namespace n
on ct.relnamespace = n.oid
where 1=1
-- and n.nspname not in ('pg_catalog','pg_toast')
and n.nspname = 'sooni'
group by n.nspname,ct.relname,ci.relname,i.indisunique,i.indisprimary
order by n.nspname,ct.relname,ci.relname
;
インデックス構成項目
table_schema | table_name | index_name | uniquenes | primarykey | column_names
--------------+----------------------+--------------------------------+-----------+------------+----------------------
sooni | address_list | addres_list_idx01 | | | city,prefectures
sooni | address_list | addres_list_idx02 | | | city
sooni | aichi | aichi_city_idx | | | city
sooni | aichi | aichi_prefectures_city_idx | | | prefectures,city
sooni | cable | cable_pkey | unique | pk | test_item,id
sooni | chiba | chiba_city_idx | | | city
sooni | chiba | chiba_prefectures_city_idx | | | city,prefectures
sooni | ex400 | idx01_ex400 | | | employee_number
sooni | fukushima | fukushima_city_idx | | | city
sooni | fukushima | fukushima_prefectures_city_idx | | | city,prefectures
sooni | gunma | gunma_city_idx | | | city
sooni | gunma | gunma_prefectures_city_idx | | | city,prefectures
sooni | hiroshima | hiroshima_city_idx | | | city
sooni | hiroshima | hiroshima_prefectures_city_idx | | | city,prefectures
sooni | hoge | hoge_pkey | unique | pk | id
sooni | hokkaido | hokkaido_city_idx | | | city
sooni | hokkaido | hokkaido_prefectures_city_idx | | | prefectures,city
sooni | hyogo | hyogo_city_idx | | | city
sooni | hyogo | hyogo_prefectures_city_idx | | | city,prefectures
sooni | kokudo_address_chome | uni_idx_kokudo_address_chome | unique | | oaza_town_chome_code
(20 行)
myposdb=#
