Sql-Server
Ubuntu Server 12.04 上的 PostgreSQL 9.3 與 Windows 7 Ultima 上的 MS SQL Server 2008 R2
我在 Google 上搜尋了很多關於 PostgreSQL 與 MS SQL 2008 R2 的性能的文章，發現其中許多文章都說 PostgreSQL 的性能優於 MSSQL 2008 R2。所以，我用我公司的真實案例做一個真實的測試。
硬體：我在 ESXi 5.5 中建構了兩個 vmware guest，每個 guest 都有相同的虛擬硬體規格，包括（我只列出關鍵項目）：
- 記憶體：4GB
- CPU：一個帶兩個核心的虛擬插槽
- 虛擬磁碟：50GB 用於 Ubuntu 伺服器，200GB 用於 Windows 7（嗯，正如你所知，微軟總是消耗更多的儲存空間）
虛擬機主機（單盒）：
- 記憶體：16GB，intel-i5 2 核，超執行緒
- 高畫質：1TGB 2.5"
軟體：
Windows 7 Ultima + MS SQL Server 2008 R2，全部採用預設參數
Ubuntu Linux Server 12.04 + PostgreSQL 9.3，全部帶預設參數
數據庫工具：Db Visualizer 9.0.9（在另一台筆記型電腦上）
JDBC：
- PostgreSQL：postgresql-9.3-1101.jdbc4
- MSSQL 2008R2：sqljdbc_4.0.2206.100_cht.tar
表模式（在 PostgreSQL 和 MSSQL 2008 R2 上相同，但命名約定除外）
我有如下三個表：
CREATE TABLE gourmet ( vendor_url CHARACTER VARYING(200), ticket_price CHARACTER VARYING(8000), tour_advice CHARACTER VARYING(8000), gourmet_name_c CHARACTER VARYING(180), tel_ext CHARACTER VARYING(5), gourmet_name_e CHARACTER VARYING(60), arrangement CHARACTER VARYING(8000), data_source CHARACTER VARYING(200), open_time CHARACTER VARYING(8000), vendor_name CHARACTER VARYING(60), gps_x CHARACTER VARYING(20), gourmet_id CHARACTER VARYING(36) NOT NULL, gps_y CHARACTER VARYING(20), updated_by CHARACTER VARYING(20), recom_level DOUBLE PRECISION, fax_num CHARACTER VARYING(10), address_c CHARACTER VARYING(240), fax_area_code CHARACTER VARYING(4), long_desc CHARACTER VARYING(8000), for_search_only BOOLEAN, status CHARACTER VARYING(1), category CHARACTER VARYING(3), area_code CHARACTER VARYING(4), county CHARACTER VARYING(3), email CHARACTER VARYING(40), updated_date TIMESTAMP(6) WITHOUT TIME ZONE, data_source_url CHARACTER VARYING(200), tour_duration CHARACTER VARYING(400), short_name CHARACTER VARYING(180), created_by CHARACTER VARYING(20), created_date TIMESTAMP(6) WITHOUT TIME ZONE, town CHARACTER VARYING(3), address_e CHARACTER VARYING(80), parking_info CHARACTER VARYING(8000), remark CHARACTER VARYING(8000), location_info CHARACTER VARYING(8000), tel_no CHARACTER VARYING(10), CONSTRAINT gourmet_pk PRIMARY KEY (gourmet_id) ); CREATE INDEX _dta_index_gourmet_5_574625090__k9_k4_k1_k10_k31_k2_11_14_15_33 ON gourmet ( county, status, gourmet_id, town, vendor_url, gourmet_name_c ); CREATE INDEX _dta_index_gourmet_7_1796201449__k9_k1_k2 ON gourmet ( county, gourmet_id, gourmet_name_c ); CREATE INDEX idx_grourmet_status ON gourmet ( status ); ======================================================= CREATE TABLE photos ( source CHARACTER VARYING(100), caption CHARACTER VARYING(100), display_order INTEGER, main_photo BOOLEAN, bytes_original BYTEA, folder_id CHARACTER VARYING(36), photo_id INTEGER DEFAULT nextval('photos_photo_id_seq'::regclass) NOT NULL, bytes_thumb BYTEA, CONSTRAINT photos_pk PRIMARY KEY (photo_id) ); CREATE INDEX _dta_index_photos_5_1925581898__k2_k5_1 ON photos ( folder_id, main_photo ); CREATE INDEX idx_photos_folder_id ON photos ( folder_id ); CREATE INDEX _dta_index_photos_5_1925581898__k5_k2_k1 ON photos ( main_photo, folder_id, photo_id ); ==================================================== CREATE TABLE counters ( item_id CHARACTER VARYING(36) NOT NULL, counter_name CHARACTER VARYING(30) NOT NULL, target_date CHARACTER(10) NOT NULL, total_hit BIGINT, CONSTRAINT counters_pk PRIMARY KEY (item_id, counter_name, target_date) );餐桌美食有4108條記錄，餐桌照片有37451條記錄，餐桌計數器有11659353條記錄。
為了避免結果集的傳輸消耗時間，所以我只計算記錄數。
PostgreSQL 中使用的 SQL 語句：
SELECT count(*) FROM ( SELECT a.gourmet_id item_id, a.gourmet_name_c item_name, a.vendor_url external_url, 'fnf-item.aspx?pid=' || a.gourmet_id fun_taiwan_url, b.photo_id,coalesce(SUM(c.total_hit),0) hit_count,to_char(a.created_date, 'YYYY/MM/DD HH24:MI:SS') created_date, '1' is_gourmet, a.for_search_only, a.area_code || '-' || a.tel_no tel_no, a.tel_ext, a.county, a.town, a.address_c , 'gourmet.png' icon_name , now() as updated_date, 'NonOrderScheduler' as updated_by FROM gourmet a LEFT JOIN photos b ON b.folder_id=a.gourmet_id AND b.main_photo='1' LEFT JOIN counters c ON c.item_id=a.gourmet_id AND c.counter_name='FnfHit' WHERE a.status='A' GROUP BY a.gourmet_id, a.gourmet_name_c, length(a.vendor_url),a.vendor_url, 'fnf-item.aspx?pid=' || a.gourmet_id,b.photo_id, to_char(a.created_date,'YYYY/MM/DD HH24:MI:SS'), a.for_search_only, a.area_code || '-' || a.tel_no, a.tel_ext,a.county, a.town, a.address_c ) tMSSQL 2008 R2 中使用的 SQL：
CHECKPOINT; GO DBCC DROPCLEANBUFFERS; --Clean Query Cache GO SELECT count(*) FROM ( SELECT a.GourmetId ItemId, a.GourmetNameC ItemName, a.VendorUrl ExternalUrl, 'fnf-item.aspx?pid=' + a.GourmetId FunTaiwanUrl, b.PhotoId,ISNULL(SUM(c.TotalHit),0) HitCount, CONVERT(VARCHAR,a.CreatedDate,111) CreatedDate, '1' IsGourmet, a.ForSearchOnly, a.AreaCode + '-' + a.TelNo TelNo, a.TelExt, a.County, a.Town, a.AddressC , 'gourmet.png' IconName , GetDate() as UpdatedDate, 'NonOrderScheduler' as UpdatedBy FROM Gourmet a LEFT JOIN Photos b ON b.FolderId=a.GourmetId AND b.MainPhoto=1 LEFT JOIN Counters c ON c.ItemId=a.GourmetId AND c.CounterName='FnfHit' WHERE a.[Status]='A' GROUP BY a.GourmetId, a.GourmetNameC, len(a.VendorUrl),a.VendorUrl, 'fnf-item.aspx?pid=' + a.GourmetId,b.PhotoId, CONVERT(VARCHAR,a.CreatedDate,111), a.ForSearchOnly, a.AreaCode + '-' + a.TelNo, a.TelExt,a.County, a.Town, a.AddressC ) t實際上，這兩條 SQL 語句除了語法和功能是相同的。
測試方法：
我從另一台筆記型電腦執行 DbVisualizer 中的 SQL 語句。vmware 主機中只有兩個 vmware guest。當我開始測試 PostgreSQL 時，Windows 7 Ultimate 沒有執行任何前台軟體，反之亦然。
在 Windows 7 Ultimate 上的 MS SQL Server 2008 中使用相同的表模式、相同的記錄和相同的 SQL 語句，我發現結果與那些說 PostgreSQL 比 MSSQL2008R2 更快的文章大不相同。
對於計算結果集大小，MSSQL2008 R2 花費 0.016 秒，而 PostgreSQL 花費 132 秒。可以看到，在 MSSQL2008R2 上執行 SQL 之前，我先清理了它的查詢記憶體，但是我沒有清理 PostgreSQL 上的查詢記憶體。
我的問題是，在這種情況下，我應該如何調整 PostgreSQL 以便它可以比 MSSQL2008 R2 更快地查詢？
我知道你們中的一些人可能會要求我使用解釋分析命令調整 SQL 語句。但是，基本上，我不想調整 SQL 語句，除非伺服器（OS + PostgreSQL）參數已調整到最佳情況，因為我計劃將公司中的所有數據從 MSSQL2008R2 移動到 PostgreSQL。因此，不可能為每個系統調整每個 SQL 語句。
如果通過調整 OS 參數和 PostgreSQL 參數可以解決我的問題，我寧願不要在短時間內調整 SQL 語句。你知道這將是一項巨大的工作。
謝謝您的幫助。
HashAggregate (cost=305063.90..328086.20 rows=920892 width=126) (actual time=4595.501..4598.155 rows=4019 loops=1) -> Nested Loop Left Join (cost=450.60..272832.68 rows=920892 width=126) (actual time=4.304..2567.521 rows=1690393 loops=1) -> Hash Right Join (cost=450.04..1230.97 rows=4132 width=118) (actual time=4.223..20.423 rows=4019 loops=1) Hash Cond: ((b.folder_id)::text = (a.gourmet_id)::text) -> Bitmap Heap Scan on photos b (cost=200.44..848.93 rows=12149 width=13) (actual time=0.896..5.782 rows=12166 loops=1) Filter: main_photo -> Bitmap Index Scan on _dta_index_photos_5_1925581898__k5_k2_k1 (cost=0.00..197.41 rows=12149 width=0) (actual time=0.840..0.840 rows=12166 loops=1) Index Cond: (main_photo = true) -> Hash (cost=199.36..199.36 rows=4019 width=114) (actual time=3.305..3.305 rows=4019 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 603kB -> Seq Scan on gourmet a (cost=0.00..199.36 rows=4019 width=114) (actual time=0.012..1.768 rows=4019 loops=1) Filter: ((status)::text = 'A'::text) Rows Removed by Filter: 90 -> Index Scan using counters_pk on counters c (cost=0.56..60.72 rows=223 width=16) (actual time=0.029..0.132 rows=421 loops=4019) Index Cond: (((item_id)::text = (a.gourmet_id)::text) AND ((counter_name)::text = 'FnfHit'::text)) Total runtime: 4600.750 msMSSQL2008R2解釋分析結果：
|--Compute Scalar(DEFINE:([Expr1013]='fnf-item.aspx?pid='+[SlowTravel].[dbo].[Gourmet].[GourmetId] as [a].[GourmetId], [Expr1014]=isnull([Expr1012],(0)), [Expr1015]='1', [Expr1016]='gourmet.png', [Expr1017]=getdate(), [Expr1018]='NonOrderScheduler')) |--Compute Scalar(DEFINE:([Expr1012]=CASE WHEN [Expr1027]=(0) THEN NULL ELSE [Expr1028] END)) |--Stream Aggregate(GROUP BY:([a].[GourmetId], [Expr1008], [Expr1009], [b].[PhotoId], [Expr1010], [Expr1011]) DEFINE:([Expr1027]=COUNT_BIG([SlowTravel].[dbo].[Counters].[TotalHit] as [c].[TotalHit]), [Expr1028]=SUM([SlowTravel].[dbo].[Counters].[TotalHit] as [c].[TotalHit]), [a].[GourmetNameC]=ANY([SlowTravel].[dbo].[Gourmet].[GourmetNameC] as [a].[GourmetNameC]), [a].[VendorUrl]=ANY([SlowTravel].[dbo].[Gourmet].[VendorUrl] as [a].[VendorUrl]), [a].[ForSearchOnly]=ANY([SlowTravel].[dbo].[Gourmet].[ForSearchOnly] as [a].[ForSearchOnly]), [a].[TelExt]=ANY([SlowTravel].[dbo].[Gourmet].[TelExt] as [a].[TelExt]), [a].[County]=ANY([SlowTravel].[dbo].[Gourmet].[County] as [a].[County]), [a].[Town]=ANY([SlowTravel].[dbo].[Gourmet].[Town] as [a].[Town]), [a].[AddressC]=ANY([SlowTravel].[dbo].[Gourmet].[AddressC] as [a].[AddressC]))) |--Nested Loops(Left Outer Join, OUTER REFERENCES:([a].[GourmetId], [Expr1026]) WITH UNORDERED PREFETCH) |--Merge Join(Left Outer Join, MERGE:([a].[GourmetId])=([b].[FolderId]), RESIDUAL:([SlowTravel].[dbo].[Photos].[FolderId] as [b].[FolderId]=[SlowTravel].[dbo].[Gourmet].[GourmetId] as [a].[GourmetId])) | |--Sort(ORDER BY:([a].[GourmetId] ASC)) | | |--Compute Scalar(DEFINE:([Expr1008]=len([SlowTravel].[dbo].[Gourmet].[VendorUrl] as [a].[VendorUrl]), [Expr1009]='fnf-item.aspx?pid='+[SlowTravel].[dbo].[Gourmet].[GourmetId] as [a].[GourmetId], [Expr1010]=CONVERT(varchar(30),[SlowTravel].[dbo].[Gourmet].[CreatedDate] as [a].[CreatedDate],111), [Expr1011]=([SlowTravel].[dbo].[Gourmet].[AreaCode] as [a].[AreaCode]+'-')+[SlowTravel].[dbo].[Gourmet].[TelNo] as [a].[TelNo])) | | |--Table Scan(OBJECT:([SlowTravel].[dbo].[Gourmet] AS [a]), WHERE:([SlowTravel].[dbo].[Gourmet].[Status] as [a].[Status]='A')) | |--Index Seek(OBJECT:([SlowTravel].[dbo].[Photos].[_dta_index_Photos_5_1925581898__K5_K2_K1] AS [b]), SEEK:([b].[MainPhoto]=(1)) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([SlowTravel].[dbo].[Counters].[Counters_PK] AS [c]), SEEK:([c].[CounterName]='FnfHit' AND [c].[ItemId]=[SlowTravel].[dbo].[Gourmet].[GourmetId] as [a].[GourmetId]) ORDERED FORWARD)如何避免在解釋結果中排序？實際上，我沒有在 SQL 語句中詢問任何排序。
謝謝您的幫助。
如何避免在解釋結果中排序？實際上，我沒有在 SQL 語句中詢問任何排序。
您已要求聚合行。一種方法是對數據集進行排序，然後對其進行掃描以折疊出重複項。這可能比散列聚合更快，這是 PostgreSQL 知道如何進行分組的另一種方式。
因此，儘管您沒有明確說“對行進行排序”，但由於您要求的某些內容，它仍在對它們進行排序。
最直接的問題是 PostgreSQL 對用於排序的記憶體量非常保守：
排序方法：外部合併磁碟：317656kB
並且正在做一個 300MB 的磁碟排序。如果你看一下explain.depesz.com 上的平原，你會很清楚地看到這一點。
如果你：
SET work_mem = '400MB';在執行查詢之前，它應該有很大的不同。
不幸的是，這不是更改配置那麼簡單，因為 PostgreSQL 在資源管理方面不太聰明。根據 上的文件，每個排序或連接，每個會話
work_mem最多可以使用work_mem字節。因此，如果您有 max_connections = 50 並且您正在執行複雜的查詢，您可能會發現自己使用了許多 GB 的工作記憶體，超出了可用記憶體，並點擊了交換。你真的不想要的。它似乎也在 seqscan 上進行
counters，但由於它發現大約 1/4 的行符合條件，這可能是正確的做法 - 索引掃描可能會更慢。我發現預設
work_mem方式過於保守，並且傾向於在任何相當大的系統上將其設置為至少 100MB。我也更喜歡在 PgBouncer 前面執行 PostgreSQL，並且使用一個低的max_connections，允許我在每個單獨的連接上投入更多的資源。坦率地說，我很想知道 MS SQL Server 是如何執行此操作的，因為您報告的數字對於這樣的查詢來說是驚人的。