PostgreSQL

PostgreSQL インデックス一覧(構成項目あり)

インデックス構成項目

インデックス名と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=#
スポンサーリンク
コピペで使う