pg資料庫創建索引

① postgresql建了索引，查詢用不上怎麼回事

問題源自一個帥哥在建索引發生表鎖的問題。先介紹一下Postgresql的建索引語法：
Version:9.1
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
這里不解釋語法的諸多參數使用(排序，使用方法，填充因子等)，主要說一下concurrently的使用場景。

② postgresql 怎麼創建點陣圖索引

sysbench原來自帶的lua數據裝載腳本是使用以下方式串列裝載的，速度比較慢(比單條insert快，但是比COPY慢)。insertintotable1values(),(),().insertintotable2values(),(),().insertintotablenvalues(),(),().使用prepare導入數據的用法舉例./sy.

③ postgres怎麼在線創建索引

我們知道資料庫創建索引可能會鎖住創建索引的表，並且用該表上的一次掃描來執行整個索引的構建，這樣在創建索引時會影響在線業務，非常大的表創建索引可能會需要幾個小時，這樣阻塞業務是不被允許的。商業資料庫一般都提供在線創建索引的能力，PostgreSQL作為開源資料庫，也提供了這樣的功能。我們在CREATE INDEX命令中新增CONCURRENTLY選項來實現索引的在線創建。 但是concurrently在線創建索引也並不是那麼完美，當使用這個選項時，PostgreSQL必須執行該表的兩次掃描，此外它必須等待所有現有可能會修改或者使用該索引的事務終止，甚至它可能會等待一個不相乾的事務終止。因此這種方法比起普通的索引創建過程來說要做更多工作並且需要更多時間。同時，索引的創建會帶來較大的CPU和I/O消耗。甚至在極端情況下，如果資料庫存在長事務，我們發現create index命令根本無法結束。 從官方文檔中我們可以了解到如下信息，在並發（concurrently）索引構建中，索引實際上是在事務中被構建的，它在兩個事務中發生兩次表掃描。在每一次表掃描之前，索引構建必須等待對該表做過修改的現有事務終止。在第二次掃描之後，索引構建必須等待任何持有早於第二次掃描的快照的事務終止。然後該索引最終能被標記為可用，CREATE INDEX命令完成。

1. 開啟第一個事務，拿到當前快照snapshot

2.等待所有修改過該表的事務結束

3.掃描該表，第一次創建索引

4.結束第一個事務

5.開啟第二個事務，拿到當前快照snapshot2

6.等待所有修改過該表的事務結束

7.第二次掃描該表，將兩次快照之間變更的記錄，合並到索引

8.上一步更新索引結束後，等待snapshot2之前開啟的所有事務結束

9.結束索引創建，索引變為可用 那麼這里有個疑問，為什麼需要兩次掃描、兩次創建索引？其實想想也很好解釋。因為在第一次創建索引的時候不阻塞讀寫，這段時間內發生的變更需要在第二次掃描的時候合並更新進索引。

④ postgresql 怎麼添加聚合索引

sysbench原來自帶的lua數據裝載腳本是使用以下方式串列裝載的，速度比較慢(比單條insert快，但是比COPY慢)。insertintotable1values(),(),().insertintotable2values(),(),().insertintotablenvalues(),(),().使用prepare導入數據的用法舉例./sysbench_pg--test=lua/oltp.lua--db-driver=pgsql--pgsql-host=127.0.0.1--pgsql-port=1921--pgsql-user=postgres--pgsql-password=postgres--pgsql-db=postgres--oltp-tables-count=64--oltp-table-size=1000000--num-threads=64prepareprepare表示裝載數據，但是它串列的。sysbench0.5中可以在命令行中指定測試時啟動的並行線程數，這個測試過程是使用run命令，而且是多線程並發的，所以我們可以使用sysbench的run命令來造數據，而不再使用其提供的prepare命令的方法來造數據。run命令會根據命令行參數--num-threads來指定並發線程數的多少。在sysbench中自定義的lua腳本中要求實現以下幾個函數：functionthread_init(thread_id):此函數在線程創建後只被執行一次functionevent(thread_id):每執行一次就會被調用一次。由上可以知道，本次造數據的腳本我們只需要實現thread_init()函數就可以了。生成測試數據的腳本沿用老唐提供的代碼：#include#include#include#include#includeuint64_tmy_rand(structrandom_data*r1,structrandom_data*r2){uint64_trand_max=100000000000LL;uint64_tresult;uint32_tu1,u2;random_r(r1,&u1);random_r(r2,&u2);result=(int64_t)u1*(int64_t)u2;result=result%rand_max;returnresult;}intmain(intargc,char*argv[]){structtimevaltpstart;structrandom_datar1,r2;inti;intr;intmax_value;charrand_state1[128];charrand_state2[128];if(argc!=2){printf("Usage:%s\n",argv[0]);return1;}max_value=atoi(argv[1]);gettimeofday(&tpstart,NULL);initstate_r(tpstart.tv_usec,rand_state1,sizeof(rand_state1),&r1);srandom_r(tpstart.tv_usec,&r1);gettimeofday(&tpstart,NULL);initstate_r(tpstart.tv_usec,rand_state2,sizeof(rand_state1),&r2);srandom_r(tpstart.tv_usec,&r2);for(i=1;i>sbtest'..table_id..'.dat&')os.execute('catsbtest'..table_id..'.dat|psql-h'..pgsql_host..'-p'..pgsql_port..'-U'..pgsql_user..'-d'..pgsql_db..'-c"sbtest'..table_id..'fromstdinwithcsv"')os.execute('rm-fsbtest'..table_id..'.dat')endfunctioncreate_index(table_id)db_query("selectsetval('sbtest"..table_id.."_id_seq',"..(oltp_table_size+1)..")")db_query("CREATEINDEXk_"..table_id.."onsbtest"..table_id.."(k)")endfunctionthread_init(thread_id)set_vars()print("threadprepare"..thread_id)fori=thread_id+1,oltp_tables_count,num_threadsdodata(i)create_index(i)endendfunctionevent(thread_id)os.exit()end用法，必須把psql放到路徑中，因為lua中需要用到psql命令exportPATH=/home/digoal/pgsql9.5/bin:$PATH生成數據，速度比以前快多了./sysbench_pg--test=lua/.lua\--db-driver=pgsql\--pgsql-host=127.0.0.1\--pgsql-port=1921\--pgsql-user=postgres\--pgsql-password=postgres\--pgsql-db=postgres\--oltp-tables-count=64\--oltp-table-size=1000000\--num-threads=64\run清除數據,droptable./sysbench_pg--test=lua/.lua\--db-driver=pgsql\--pgsql-host=127.0.0.1\--pgsql-port=1921\--pgsql-user=postgres\--pgsql-password=postgres\--pgsql-db=postgres\--oltp-tables-count=64\--oltp-table-size=1000000\--num-threads=64\cleanuplua全局變數代碼：sysbench/scripting/lua/src/lua.h:#definelua_register(L,n,f)(lua_pushcfunction(L,(f)),lua_setglobal(L,(n)))sysbench/scripting/lua/src/lua.h:#definelua_setglobal(L,s)lua_setfield(L,LUA_GLOBALSINDEX,(s))sysbench/scripting/lua/src/lbaselib.c:lua_setglobal(L,"_G");sysbench/scripting/lua/src/lbaselib.c:lua_setglobal(L,"_VERSION");/*setglobal_VERSION*/sysbench/scripting/lua/src/lbaselib.c:lua_setglobal(L,"newproxy");/*setglobal`newproxy'*/sysbench/scripting/script_lua.c:lua_setglobal(state,opt->name);sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_uniq");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rnd");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_str");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_uniform");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_gaussian");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_special");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_connect");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_disconnect");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_query");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bulk_insert_init");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bulk_insert_next");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bulk_insert_done");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_prepare");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bind_param");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bind_result");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_execute");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_close");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_store_results");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_free_results");sysbench/scripting/script_lua.c:lua_setglobal(state,"DB_ERROR_NONE");sysbench/scripting/script_lua.c:lua_setglobal(state,"DB_ERROR_DEADLOCK");sysbench/scripting/script_lua.c:lua_setglobal(state,"DB_ERROR_FAILED");sysbench/scripting/script_lua.c:lua_setglobal(L,"db_driver");傳入參數，可以把sysbench_pg的參數-替換成_在lua腳本中使用這些變數，例子--pgsql-host=127.0.0.1->對應lua中的變數名pgsql_host--pgsql-port=1921->對應lua中的變數名pgsql_port--pgsql-user=postgres->對應lua中的變數名pgsql_user--pgsql-password=postgres->對應lua中的變數名pgsql_password--pgsql-db=postgres->對應lua中的變數名pgsql_db--oltp-tables-count=64->對應lua中的變數名oltp_tables_count--oltp-table-size=1000000->對應lua中的變數名oltp_table_size--num-threads=64->對應lua中的變數名num_threads

⑤ postgresql 資料庫日期可以添加什麼類型的索引

可以建立索引的；至於建立聚集索引或者是非聚集索引，那要看你這個時間欄位的具體情況以及使用或變更頻繁程度。 一般來說，適合建立聚集索引的要求：「既不能絕大多數都相同，又不能只有極少數相同」的規則。

⑥ pgadmin工具怎麼創建索引

1、通過命令行查詢 \d 資料庫 —— 得到所有表的名字 \d 表名 —— 得到表結構 2、通過SQL語句查詢 "select * from pg_tables" —— 得到當前db中所有表的信息（這里pg_tables是系統視圖）

⑦ PG資料庫不走索引

用以下sql就可以做到：
SELECT
schemaname = OBJECT_SCHEMA_NAME(o.object_id)
,tablename = o.NAME
FROM sys.objects o
INNER JOIN sys.indexes i ON i.OBJECT_ID = o.OBJECT_ID
WHERE (
o.type = 'U'
AND o.OBJECT_ID NOT IN (
SELECT OBJECT_ID
FROM sys.indexes
WHERE index_id > 0
)
)

⑧ PG里如何查看錶，索引，表空間，資料庫大小

--查詢一個索引大小 select pg_size_pretty(pg_relation_size('indexname))--查看一張表及此它上的索引總大小 select pg_size_pretty(pg_total_relation_size('tablename')); --查看所有 schema裡面索引大小，大到小的順序排列： select indexrelname,pg_size_pretty( pg_relation_size(relid)) from pg_stat_user_indexes where schemaname = 'schemaname' order by pg_relation_size(relid) desc;--查看所有 schema裡面表的大小，從大到小順序排列： select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname = 'schemaname' order by pg_relation_size(relid) desc; --查看資料庫大小： select pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;--查看錶空間大小

⑨ postgresql 查看錶建立哪些索引

在資料庫運維工作中，經常會有數據目錄使用率較高需要調整的情況，通常會給資料庫建立多個表空間，
並分別位於不同的盤上，這時需要做的工作就是調整庫中現有表和索引的表空間，下面簡單總結下這塊維護
工作的內容，以下都是基於 PostgreSQL 9.0.1 做的測試。

一 查詢某個表所在表空間的簡單方法

PostgreSQL 提供類似" \ "命令很方便得到相關信息，命令如下：
skytf=> \d test_2
Table "skytf.test_2"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
obj_id | integer | not null
name | character varying(64) |
Indexes:
"idx_hash_name" hash (name)
"idx_test_2" btree (id, obj_id)
Tablespace: "tbs_skytf_idx"
備註：如果這個表的表空間為當前資料庫的默認表空間，那麼上面則不會顯示 Tablespace 信息，
相反，則會顯示這張有的表空間，例如上面的表 test_2 的表空間為 tbs_skytf_idx，而
表空間 "tbs_skytf_idx" 不是資料庫 skytf 的默認表空間， 那麼如何查詢資料庫的默認
表空間呢，可以通過以下命令查詢。

--1.1 查詢資料庫的默認表空間
skytf=> select datname,dattablespace from pg_database where datname='skytf';
datname | dattablespace
---------+---------------
skytf | 14203070
(1 row)
skytf=> select oid,spcname from pg_tablespace where oid=14203070;
oid | spcname
----------+-----------
14203070 | tbs_skytf
(1 row)
備註：通過以上查出資料庫 skytf 的默認表空間為 tbs_skytf。

二 批量查詢資料庫表和索引的表空間
--2.1 查詢表和索引所在的表空間
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname
from pg_class a, pg_tablespace tb
where a.reltablespace = tb.oid
and a.relkind in ('r', 'i')
order by a.relpages desc;

備註：上面只取了部分結果，這個查詢能夠查詢表和索引所處的表空間，但是有一點需要注意，這個查詢
僅顯示表空間不是資料庫默認表空間的資料庫對像，而我們通常需要查出位於資料庫默認表空間的
對像，顯然上面的查詢不是我們想要的，接下來看另一個查詢。

--2.2 查詢位於默認資料庫表空間的對像
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a
where a.relkind in ('r', 'i')
and reltablespace='0'
order by a.relpages desc;

備註：這個查詢加入限制條件 reltablespace='0'，即可查找出位於當前資料庫默認表空間的
資料庫表和索引。 通常這才是我們想要的結果，接下來可以把部分表轉移到其它表空間上去，轉移
的方法可以用 "ALTER TABLE move tablespace "或者重建索引移表空間等方法，這里不詳細介紹。

--2.3 查詢在某個表空間上的對像

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a, pg_tablespace tb
where a.relkind in ('r', 'i')
and a.reltablespace=tb.oid
and tb.spcname='tablespace_name'
order by a.relpages desc;

--2.4 手冊上對於 pgclass 視圖的 reltablespace 欄位解釋
The tablespace in which this relation is stored. If zero, the database is default tablespace is
implied. (Not meaningful if the relation has no on-disk file.)