由於臨時文件，查詢性能變慢？

這是查詢：

SELECT "products".* 
FROM "products"
WHERE (status > 100) 
AND "products"."above_revenue_average" = 't' 
AND ("products"."category_id" NOT IN (5))
ORDER BY "products"."start_date" DESC 

我在status和上有一個索引start_date。

每次從我的應用程序執行查詢時，我都會在日誌中得到以下資訊：

[WHITE] temporary file:
path "pg_tblspc/16386/PG_9.3_201306121/pgsql_tmp/pgsql_tmp2544.0", size 37093376 
Query: SELECT "products".* FROM "products"  WHERE (status > 100) 
AND "products"."above_revenue_average" = 't' 
AND ("products"."category_id" NOT IN (5))  ORDER BY "products"."start_date" DESC 

我相信這個臨時文件創建是性能緩慢的原因。

執行EXPLAIN ANALYZE我得到以下結果：

QUERY PLAN
Sort  (cost=63395.28..63403.51 rows=16460 width=524)
      (actual time=524.134..562.635 rows=65294 loops=1)
  Sort Key: start_date
  Sort Method: external merge  Disk: 36224kB
  ->  Bitmap Heap Scan on products  
       (cost=4803.40..60389.73 rows=16460 width=524)
       (actual time=27.390..397.879 rows=65294 loops=1)
        Recheck Cond: (status > 100)
        Filter: (above_revenue_average AND (category_id <> 5))
        Rows Removed by Filter: 25115
        ->  Bitmap Index Scan on index_products_on_status
           (cost=0.00..4802.58 rows=89662 width=0) 
           (actual time=18.006..18.006 rows=90411 loops=1)
              Index Cond: (status > 100)
Total runtime: 577.870 ms
(10 rows)

然後我使用http://explain.depesz.com/使其更具可讀性：

每個節點類型統計資訊

+-------------------+-------+--------------+------------+
|     node type     | count | sum of times | % of query |
+-------------------+-------+--------------+------------+
| Bitmap Heap Scan  |     1 | 379.873 ms   | 67.5 %     |
| Bitmap Index Scan |     1 | 18.006 ms    | 3.2 %      |
| Sort              |     1 | 164.756 ms   | 29.3 %     |
+-------------------+-------+--------------+------------+

每桌統計

+------------------+------------+--------------+------------+
|    Table name    | Scan count |  Total time  | % of query |
+------------------+------------+--------------+------------+
| scan type        | count      | sum of times | % of table |
| products         | 1          | 379.873 ms   | 67.5 %     |
| Bitmap Heap Scan | 1          | 379.873 ms   | 100.0 %    |
+------------------+------------+--------------+------------+

我可以通過添加更多索引來提高數據庫性能嗎？也許一些複合的？有任何想法嗎？

work_mem

顯然，排序操作溢出到磁碟：

Sort Method: external merge  Disk: 36224kB

更多work_mem可以幫助查詢，例如@Kassandry 已經建議。增加設置，直到您看到Memory而不是Disk在EXPLAIN輸出中。但是根據一個查詢增加正常設置可能是個壞主意。正確的設置取決於可用的 RAM 和您的完整情況。從閱讀Postgres Wiki開始。

要修復您的查詢，work_mem請為您的事務設置足夠高的值，僅SET LOCAL在同一事務中。

BEGIN;

SET LOCAL work_mem = '45MB';

SELECT ...

COMMIT;   -- or ROLLBACK

您可能需要超過 40 MB。記憶體中的表示比磁碟上的表示要大一點。有關的：

• Linux 上 PostgreSQL 中的配置參數 work_mem

詢問

您的查詢（修剪一些噪音後）：

SELECT * 
FROM   products
WHERE  status > 100
AND    above_revenue_average  -- boolean can be used directly
AND    category_id <> 5
ORDER  BY start_date DESC;

您的行是半千字節寬 ( width=524)。您需要返回所有列嗎？（通常情況下，您不需要。）僅列出SELECT查詢中需要的列以提高整體性能，尤其是在您已經遇到work_mem問題的情況下。

任何涉及的列都可以為 NULL 嗎？category_id對於和尤其重要start_date。在這種情況下，您可能需要適應…

指數

多列索引當然可以提高性能。（就像@Paul 概述的那樣）。你必須權衡成本和收益。如果這個查詢的性能很重要或者很常見，那就去吧。不要為每個查詢創建一個特殊的索引。盡可能少，盡可能多。共享時索引更強大，這增加了更多索引保留在記憶體中的機會。

booleanlike 列是above_revenue_average部分索引中條件的典型候選者，而不是索引列。

• 對值為 NULL 的布爾值進行查詢時出現意外的 Seq 掃描

我基於不完整資訊的瘋狂猜測：

CREATE INDEX prod_special_idx ON products (start_date DESC)
WHERE  above_revenue_average
AND    status > 100
AND    category_id <> 5;

DESC NULLS LAST在索引和查詢中使用是否start_date可以為 NULL。

• PostgreSQL 按日期時間 asc 排序，先為空？

引用自：https://dba.stackexchange.com/questions/112079