Postgresql

索引不與表繼承一起使用

  • August 17, 2015

我有一個帶有主表和 2 個子表的 PostgreSQL 9.0.12 數據庫。我的桌子：

CREATE TABLE test2 (
   id serial PRIMARY KEY,
   coll character varying(15),
   ts timestamp without time zone
);
CREATE INDEX ON test2(ts);

CREATE TABLE test2_20150812 (
   CHECK ( ts >= timestamp '2015-08-12' AND ts < timestamp '2015-08-13' )
) INHERITS (test2);

CREATE TABLE test2_20150811 (
   CHECK ( ts >= timestamp '2015-08-11' AND ts < timestamp '2015-08-12' )
) INHERITS (test2);

CREATE INDEX ON test2_20150812(ts);
CREATE INDEX ON test2_20150811(ts);
VACUUM FULL ANALYZE;

我的選擇查詢的解釋結果（數據庫中沒有任何行）：

EXPLAIN (ANALYZE, BUFFERS) select * from test2 WHERE ts >= '2015-08-11' ORDER BY ts DESC;

Sort  (cost=89.87..92.09 rows=887 width=31) (actual time=0.245..0.245 rows=0 loops=1)
  Sort Key: public.test2.ts
  Sort Method:  quicksort  Memory: 17kB
  Buffers: shared read=2
  ->  Result  (cost=0.00..46.44 rows=887 width=31) (actual time=0.087..0.087 rows=0 loops=1)
        Buffers: shared read=2
        ->  Append  (cost=0.00..46.44 rows=887 width=31) (actual time=0.078..0.078 rows=0 loops=1)
              Buffers: shared read=2
              ->  Seq Scan on test2  (cost=0.00..0.00 rows=1 width=31) (actual time=0.007..0.007 rows=0 loops=1)
                    Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
              ->  Bitmap Heap Scan on test2_20150812 test2  (cost=7.68..23.22 rows=443 width=31) (actual time=0.024..0.024 rows=
0 loops=1)
                    Recheck Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                    Buffers: shared read=1
                    ->  Bitmap Index Scan on test2_20150812_ts_idx  (cost=0.00..7.57 rows=443 width=0) (actual time=0.016..0.016
rows=0 loops=1)
                          Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                          Buffers: shared read=1
              ->  Bitmap Heap Scan on test2_20150811 test2  (cost=7.68..23.22 rows=443 width=31) (actual time=0.033..0.033 rows=
0 loops=1)
                    Recheck Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                    Buffers: shared read=1
                    ->  Bitmap Index Scan on test2_20150811_ts_idx  (cost=0.00..7.57 rows=443 width=0) (actual time=0.026..0.026
rows=0 loops=1)
                          Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                          Buffers: shared read=1
Total runtime: 0.320 ms
(23 rows)

但是，如果我將列coll從更改character varying(15)為character varying(255)，然後再次執行這些步驟；

CREATE TABLE test2 (
   id serial PRIMARY KEY,
   coll character varying(255),
   ts timestamp without time zone
);

解釋輸出是（數據庫中沒有任何行）：

EXPLAIN (ANALYZE, BUFFERS) select * from test2 WHERE ts >= '2015-08-11' ORDER BY ts DESC;

Sort  (cost=42.47..43.18 rows=287 width=157) (actual time=0.028..0.028 rows=0 loops=1)
  Sort Key: public.test2.ts
  Sort Method:  quicksort  Memory: 17kB
  ->  Result  (cost=0.00..30.75 rows=287 width=157) (actual time=0.020..0.020 rows=0 loops=1)
        ->  Append  (cost=0.00..30.75 rows=287 width=157) (actual time=0.015..0.015 rows=0 loops=1)
              ->  Seq Scan on test2  (cost=0.00..0.00 rows=1 width=157) (actual time=0.003..0.003 rows=0 loops=1)
                    Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
              ->  Seq Scan on test2_20150812 test2  (cost=0.00..15.38 rows=143 width=157) (actual time=0.002..0.002 rows=0 loops
=1)
                    Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
              ->  Seq Scan on test2_20150811 test2  (cost=0.00..15.38 rows=143 width=157) (actual time=0.002..0.002 rows=0 loops
=1)
                    Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
Total runtime: 0.063 ms
(12 rows)

在這種新情況下，有什麼方法可以在子表上使用索引？

所有這些都與繼承和分區無關。它一般是關於索引和查詢計劃的。

第二次嘗試的行大小要大得多：width=157與width=46. Postgres將更容易為更寬的行使用索引。意外順序掃描的可能原因包括：

  • 正如規劃師估計的那樣，您的表中第二次測試的行數要少得多：rows=143與rows=357. 只查找幾行進行排序的索引是不值得的。
  • 或者統計數據已經過時導致規劃師估計錯誤（Postgres 只認為行數會更少）。
  • 作為重寫表的副作用，索引大小可能已經膨脹。REINDEX或者VACUUM FULL會修復它。

在所有涉及的表上執行ANALYZE並重試 - 所有表中的行數相同。您應該再次看到點陣圖索引掃描。如果現象持續存在，請提供 的輸出EXPLAIN (ANALYZE, BUFFERS)，而不僅僅是EXPLAIN。

問題更新後

只要您閱讀整個表格，索引的用途就有限。如果您使用匹配索引查詢單個表，以便可以從索引中讀取已排序的行，並且 Postgres 可以完全跳過排序步驟，您將看到索引掃描。

當必須組合多個表時，這是不可能的。這個SQL fiddle每個子節點有 10k 行，並且有效統計資訊按預期顯示點陣圖索引掃描。在重複查詢幾次之後（一旦整個表被記憶體），Postgres 可能會跳過索引並切換到順序掃描，這已經變得更便宜了。

Postgres 顯然不夠聰明，無法理解相互排除的檢查約束，這將允許從每個表中按原樣附加易於排序的結果。您可以通過手動指示來強制執行此操作：

(SELECT * FROM test2_20150812 ORDER BY ts DESC)
UNION ALL
(SELECT * FROM test2_20150811 ORDER BY ts DESC);

但是，Postgres 應該足夠聰明，可以使用Merge Append（組合預排序集的廉價方法）。在我對 PostgreSQL 9.4的本地測試中，我實際上看到了每個分區上的索引掃描，並結合了Merge Append。該計劃更好，但並不比順序掃描快多少，因為記住！只要您讀取整個表，索引的使用是有限的。

'QUERY PLAN'
'Merge Append  (cost=0.73..16866.41 rows=200001 width=45)'
'  Sort Key: test.ts'
'  ->  Index Scan Backward using test_ts_idx on test  (cost=0.13..8.14 rows=1 width=528)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'
'  ->  Index Scan Backward using test_20150811_ts_idx on test_20150811  (cost=0.29..6594.01 rows=100000 width=45)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'
'  ->  Index Scan Backward using test_20150812_ts_idx on test_20150812  (cost=0.29..6594.29 rows=100000 width=45)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'

我沒有得到與 Postgres 9.3 相同的計劃（在 sqlfiddle 上測試）。必須是 pg 9.3 的限制。（？）

但是由於您使用的是過時的 9.0 版本，因此您無法使用這些版本。

Merge Append 是在 9.1 中引入的。

將結果限制為幾行時，您會得到更有趣的結果。varchar(15)或者varchar(255)對查詢計劃影響很小。更廣泛的類型更傾向於索引。

您添加了一些更多的測試查詢。

關於在 SQL Fiddle 上測試索引：

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

相關問答

Postgresql

繼承的表和索引的性能問題

  • August 14, 2015
檢視問答
Postgresql

為 WHERE 和 ORDER BY 創建多列索引

  • December 5, 2021
檢視問答
Postgresql

postgresql 計劃器/優化器的特定索引問題

  • November 18, 2021
檢視問答
Postgresql

索引不與 = ANY() 一起使用，但與 IN 一起使用

  • July 25, 2021
檢視問答
Postgresql

Postgres分區的幾個問題（分層分區、HASH、PK順序）

  • July 11, 2021
檢視問答
Postgresql

帶有點陣圖索引掃描的查詢計劃中的“重新檢查條件：”行

  • May 1, 2021
檢視問答