Postgresql查詢效率計算初探_PostgreSQL

來源:腳本之家  責任編輯:小易  

postgresql(8.2)的配置文件中有一個參數log_min_duration_statement,意思是只log執行時間大于設定值的語句,如果設為0,表示log所有語句;如果設為-1,表示不log任何語句。看起來,這個配置選項對性能的調整是很有用的,比如可以設置:log_min_duration_statement=1000則只log執行時間大于1s的語句,重點優化這些sql語句就好了。然而,奇怪的,這個選項不太容易生效!經過反復試驗,原來需要如下配置:debug_print_parse=offdebug_print_rewritten=offdebug_print_plan=offdebug_pretty_print=offlog_connections=offlog_disconnections=offlog_duration=offlog_line_prefix='%t[%p]:[%l-1]'#Special values:u=user named=database namer=remote host and porth=remote hostp=PIDt=timestamp(no milliseconds)m=timestamp with millisecondsi=command tagc=session idl=session line numbers=session start timestampx=transaction idq=stop here in non-sessionprocesses'%'e.g.'<%u%%%d>'log_statement='none'#none,mod,ddl,alllog_statement='all'#none,mod,ddl,alllog_hostname=off注意看上面的其中兩個選項的設置:log_duration=offlog_statement='none'這兩個選項的意思是不log任何sql語句和執行時間,但是恰恰是關閉了這兩個,log_min_duration_statement才會生效!可能postgresql內部 對這兩個選項做了“互斥”處理吧www.13333515.buzz防采集請勿采集本網。

摘要

關系數據庫很重要的一個方面是查詢速度。查詢速度的好壞,直接影響一個系統的好壞。

SERIAL類型的字段和MySQL中的自增唯一ID等價。當你在你的數據表中定義了一個SERIAL類型的列后,SERIAL的自增功能會被自動添加到數據庫

查詢速度一般需要通過查詢規劃來窺視執行的過程。

解決辦法:首先盡量避免模糊查詢,如果因為業務需要一定要使用模糊查詢,則至少保證不要使用全模糊查詢,對于右模糊查詢,即like‘…%’,是會使用索引的;左模糊like‘%.’無法直接使用索引,但

查詢路徑會選擇查詢代價最低的路徑執行。而這個代價是怎么算出來的呢。

將里面的python和interfrated terminal/console的配置看看,主要差別就是cwd那后面的路徑。放上去解釋說就是個運行路徑,默認是null,要設為和python一樣的workspace這才正常了!

主要關注的參數和表

order by后加個limit1 select.from.order by.limit 1

參數:來自postgresql.conf文件,可以通過show 來查看

也支持 like '%xx%',full text 是內建在SQL 引擎中的,不需要另外開啟,速度可以提升30x左右。

seq_page_cost = 1.0 # measured on an arbitrary scalerandom_page_cost = 4.0 # same scale as abovecpu_tuple_cost = 0.01 # same scale as abovecpu_index_tuple_cost = 0.005 # same scale as abovecpu_operator_cost = 0.0025 # same scale as aboveparallel_tuple_cost = 0.1 # same scale as aboveparallel_setup_cost = 1000.0 # same scale as above

表(視圖): pg_class(主要關注relpages, reltuples), pg_stats

分析簡單的查詢的成本計算過程

建立模擬數據,插入100000條數據進入一個表

create table test(id int, info text);insert into test(id, info) select i, md5(i::text) from generate_series(1, 100000) t(i);

沒有索引的情況

分析全表查詢的成本計算過程

postgres=# analyze test; #防止沒有分析postgres=# explain select * from test; QUERY PLAN ------------------------------------------------------------- Seq Scan on test (cost=0.00..1834.00 rows=100000 width=37)

1.查詢pg_class表,查看test表的page數量和行數

postgres=# select t.relpages, t.reltuples from pg_class t where t.relname = 'test'; relpages | reltuples ----------+----------- 834 | 100000

成本為1834.00是怎么算出來的?

2.這個過程,實際上是順序掃描了834個page,節點發射了100000行

3.查看配置參數

seq_page_cost = 1.0 cpu_tuple_cost = 0.01

4.得出的結果就是

postgres=# select 834 * 1.0 + 100000 * 0.01; ?column? ---------- 1834.00

5.得出來的查詢成本就是 1834.00。和上面的查詢計劃算出來的一致。

全表加入條件的成本計算過程

postgres=# explain select * from test where id = 100; QUERY PLAN -------------------------------------------------------- Seq Scan on test (cost=0.00..2084.00 rows=1 width=37) Filter: (id = 100)

成本 2084.00是怎么算出來的?

1.查詢pg_class表, pages,tuples和上面的例子一樣

2.這個過程就是順序test表,發射100000行,然后通過云存過濾了100000行

3.查看過濾運算一行的代價

cpu_operator_cost = 0.0025

4.得出的結果是

postgres=# select 834 * 1.0 + 100000 * 0.01 + 100000 * 0.0025; ?column? ----------- 2084.0000

加入索引的情況

```create index on test(id);```

對比下面的四種情況

Index Only Scan

postgres=# explain select id from test where id = 100; QUERY PLAN ----------------------------------------------------------------------------- Index Only Scan using test_id_idx on test (cost=0.29..8.31 rows=1 width=4) Index Cond: (id = 100)

Index Scan

postgres=# explain select * from test where id = 100; QUERY PLAN ------------------------------------------------------------------------- Index Scan using test_id_idx on test (cost=0.29..8.31 rows=1 width=37) Index Cond: (id = 100)

Index Scan

postgres=# explain select * from test where id < 100; QUERY PLAN ---------------------------------------------------------------------------- Index Scan using test_id_idx on test (cost=0.29..10.11 rows=104 width=37) Index Cond: (id < 100)

把數據亂序插入

truncate table test;insert into test(id, info) select i, md5(i::text) from generate_series(1, 1000000) t(i) order by random();

postgres=# explain select * from test where id < 100; QUERY PLAN ---------------------------------------------------------------------------- Bitmap Heap Scan on test (cost=5.22..380.64 rows=102 width=37) Recheck Cond: (id < 100) -> Bitmap Index Scan on test_id_idx (cost=0.00..5.19 rows=102 width=0) Index Cond: (id < 100)

結論

有索引的時候,成本會大大減少。 執行計劃跟數據的分布有很大的關系。 有索引的分析相對復雜一點,可以先參考官方源碼實現。后面再補充上來

總結

以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,謝謝大家對真格學網的支持。

一、使用EXPLAIN:PostgreSQL為每個查詢都生成一個查詢規劃,因為選擇正確的查詢路徑對性能的影響是極為關鍵的。PostgreSQL本身已經包含了一個規劃器用于尋找最優規劃,我們可以通過使用EXPLAIN命令來查看規劃器為每個查詢生成的查詢規劃。PostgreSQL中生成的查詢規劃是由1到n個規劃節點構成的規劃樹,其中最底層的節點為表掃描節點,用于從數據表中返回檢索出的數據行。然而,不同的掃描節點類型代表著不同的表訪問模式,如:順序掃描、索引掃描,以及位圖索引掃描等。如果查詢仍然需要連接、聚集、排序,或者是對原始行的其它操作,那么就會在掃描節點"之上"有其它額外的節點。并且這些操作通常都有多種方法,因此在這些位置也有可能出現不同的節點類型。EXPLAIN將為規劃樹中的每個節點都輸出一行信息,顯示基本的節點類型和規劃器為執行這個規劃節點計算出的預計開銷值。第一行(最上層的節點)是對該規劃的總執行開銷的預計,這個數值就是規劃器試圖最小化的數值。這里有一個簡單的例子,如下:復制代碼 代碼如下:EXPLAIN SELECT*FROM tenk1;QUERY PLANSeq Scan on tenk1(cost=0.00.458.00 rows=10000 width=244)EXPLAIN引用的數據是:1).預計的啟動開銷(在輸出掃描開始之前消耗的時間,比如在一個排序節點里做排續的時間)。2).預計的總開銷。3).預計的該規劃節點輸出的行數。4).預計的該規劃節點的行平均寬度(單位:字節)。這里開銷(cost)的計算單位是磁盤頁面的存取數量,如1.0將表示一次順序的磁盤頁面讀取。其中上層節點的開銷將包括其所有子節點的開銷。這里的輸出行數(rows)并不是規劃節點處理/掃描的行數,通常會更少一些。一般而言,頂層的行預計數量會更接近于查詢實際返回的行數。現在我們執行下面基于系統表的查詢:復制代碼 代碼如下:SELECT relpages,reltuples FROM pg_class WHERE relname='tenk1';從查詢結果中可以看出tenk1表占有358個磁盤頁面和10000條記錄,然而為了計算cost的值,我們仍然需要知道另外一個系統參數值。復制代碼 代碼如下:postgres=show cpu_tuple_cost;cpu_tuple_cost0.01(1 row)cost=358(磁盤頁面數)+10000(行數)*0.01(cpu_tuple_cost系統參數值)下面我們再來看一個帶有WHERE條件的查詢規劃。復制代碼 代碼如下:EXPLAIN SELECT*FROM tenk1 WHERE unique1;QUERY PLANSeq Scan on tenk1(cost=0.00.483.00 rows=7033 width=244)Filter:(unique1)EXPLAIN的輸出顯示,WHERE子句被當作一個"filter"應用,這表示該規劃節點將掃描表中的每一行數據,之后再判定它們是否符合過濾的條件,最后僅輸出通過過濾條件的行數。這里由于WHERE子句的存在,預計的輸出行數減少了。即便如此,掃描仍將訪問所有10000行數據,因此開銷并沒有真正降低,實際上它還增加了一些因數據過濾而產生的額外CPU開銷。上面的數據只是一個預計數字,即使是在每次執行ANALYZE命令之后也會隨之改變,因為ANALYZE生成的統計數據是通過從該表中隨機抽取的樣本計算的。如果我們將上面查詢的條件設置的更為嚴格一些的話,將會得到不同的查詢規劃,如:復制代碼 代碼如下:EXPLAIN SELECT*FROM tenk1 WHERE unique1;QUERY PLAN內容來自www.13333515.buzz請勿采集。


  • 本文相關:
  • postgresql中使用dblink實現跨庫查詢的方法
  • 在postgresql中實現遞歸查詢的教程
  • postgresql樹形結構的遞歸查詢示例
  • pgsql查詢優化之模糊查詢實例詳解
  • sql server數據遷移至postgresql出錯的解釋以及解決方案
  • postgresql中使用dblink實現跨庫查詢的方法
  • linux下創建postgresql數據庫的方法步驟
  • postgresql圖(graph)的遞歸查詢實例
  • postgresql教程(四):數據類型詳解
  • 在windows下手動初始化postgresql數據庫教程
  • postgresql數據庫中跨庫訪問解決方案
  • postgresql教程(十五):系統表詳解
  • postgresql樹形結構的遞歸查詢示例
  • postgresql數據庫事務實現方法分析
  • 如何測試很多個用戶同時查詢postgresql數據庫時的效率
  • 如何查看PostgreSQL執行效率低的SQL
  • PostgreSQL 查詢效率
  • 如何查看PostgreSQL執行效率低的SQL
  • 如何查看PostgreSQL執行效率低的SQL
  • Postgresql慢查詢原因查找
  • 如何快速用python查詢postgresql數據庫的行數
  • postgresql 怎么查詢第一條數據
  • postgresql 模糊查詢命令???
  • postgresql 模糊查詢
  • 網站首頁網頁制作腳本下載服務器操作系統網站運營平面設計媒體動畫電腦基礎硬件教程網絡安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess數據庫文摘數據庫其它首頁postgresqlpostgresql中使用dblink實現跨庫查詢的方法在postgresql中實現遞歸查詢的教程postgresql樹形結構的遞歸查詢示例pgsql查詢優化之模糊查詢實例詳解sql server數據遷移至postgresql出錯的解釋以及解決方案postgresql中使用dblink實現跨庫查詢的方法linux下創建postgresql數據庫的方法步驟postgresql圖(graph)的遞歸查詢實例postgresql教程(四):數據類型詳解在windows下手動初始化postgresql數據庫教程postgresql數據庫中跨庫訪問解決方案postgresql教程(十五):系統表詳解postgresql樹形結構的遞歸查詢示例postgresql數據庫事務實現方法分析postgresql 角色與用戶管理介紹windows下postgresql數據庫的下載windows下postgresql安裝圖解15個postgresql數據庫實用命令分postgresql中的oid和xid 說明windows postgresql 安裝圖文教程postgresql alter語句常用操作小postgresql 安裝和簡單使用postgresql 創建表分區postgresql新手入門教程postgresql 數據庫性能提升的幾個方面postgresql備份和增量恢復方案postgresql中調用存儲過程并返回數據集實postgresql管理工具phppgadmin入門指南windows上postgresql安裝配置教程sqlite教程(七):數據類型詳解postgresql教程(十一):服務器配置phppgadmin 配置文件參數說明中文版postgresql樹形結構的遞歸查詢示例postgresql 查看數據庫,索引,表,表空間
    免責聲明 - 關于我們 - 聯系我們 - 廣告聯系 - 友情鏈接 - 幫助中心 - 頻道導航
    Copyright © 2017 www.13333515.buzz All Rights Reserved
    3排列五开奖结果