PostgreSQLデータベースがあり、インデックスとパーティション化を使用するときに奇妙な動作を見つけました。エンジンのバージョンは10.21です。
これで、次の構造のテーブルがあります。
guid varchar(50) PK
guid_a varchar(50)
data text
part_key varchar(2)
他の列がありますが、問題ではありません。このテーブルで実行する必要があるクエリは次のとおりです。
select * from mytable where guid_a = 'jxxxxx-xxxxxxx' and data like '%7263628%';
説明しましょう。 guid_a列には、「jxxxx-xxxxxxx」の形式で人を識別するコードが含まれています。ここで、「x」は数字です。最初の2桁の数字の範囲は00から99です。たとえば、次のようになります。
j01xxx-xxxxxx
j02xxx-xxxxxx
...
j99xxx-xxxxxx
この列にインデックスを作成し、 trgm モジュールを使用してデータ列にもインデックスを作成しました。クエリを開始した後のパフォーマンスが大幅に向上しました。今まではそんなに良くなった。
私もパーティショニングを使用することにしました（テーブルでは640万レコード）そして私は、guid_a値の最初の2桁だけを含むpart_key列に99個のパーティション（リストベース）を作成しました。パーティションごとに平均65,000行の99パーティションが得られました。各パーティションには、前に説明したのと同じインデックスがあります。パフォーマンスが再び改善されました。明らかに、ファイルクエリにはpart_keyの別の条件があり、エンジンがクエリするパーティションを知ることができます。
それでは奇妙なことをしましょう。パーティションなしでテーブルにtrgmインデックスを削除しましたが、驚くほど速くなりました。分割テーブルよりはるかに高速です。パーティション表の trgm 索引も削除します。
説明で私が見つけたのは、パーティション化されていないテーブルに対するクエリは、エンジンがインデックススキャンのみを実行するように強制することです（それでは、データテーブルの2番目の条件に対する別のスキャンが必要ではないでしょうか？）。
一方、分割されたテーブルでは、Hitman インデックススキャン、ヒープスキャン、追加を順番に実行します。これは、640万行すべてを索引付けするよりも確かにコストがかかります。
異なる値で複数のテストを行ったが、結果は同じであった。
パフォーマンス:
一般的に：
パーティション表の11ms guid_aに1つの索引のみを持つ非パーティション表から9ms 2つの索引（trgmを使用するデータ列の2番目の索引）を持つ非パーティション表から20ms。
ここで何が起こっているのでしょうか？