臨時表上的索引使用情況

我有兩個相當簡單的查詢。第一個查詢

UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;

這是計劃

duration: 0.125 ms  plan:
   Query Text: UPDATE mp_physical  SET periodic_number = '' WHERE periodic_number is NULL;
   Update on mp_physical  (cost=0.42..7.34 rows=1 width=801)
     ->  Index Scan using "_I_periodic_number" on mp_physical  (cost=0.42..7.34 rows=1 width=801)
           Index Cond: (periodic_number IS NULL)

第二個：

UPDATE observations_optical_temp SET designation = '' WHERE periodic_number is NULL;

它的計劃：

duration: 2817.375 ms  plan:
   Query Text: UPDATE observations_optical_temp SET periodic_number = '' WHERE periodic_number is NULL;
   Update on observations_optical_temp  (cost=103.55..9223.01 rows=5049 width=212)
     ->  Bitmap Heap Scan on observations_optical_temp  (cost=103.55..9223.01 rows=5049 width=212)
           Recheck Cond: (periodic_number IS NULL)
           ->  Bitmap Index Scan on "_I_per_num_temp"  (cost=0.00..102.29 rows=5049 width=0)
                 Index Cond: (periodic_number IS NULL)

我希望第二個計劃與第一個計劃相同。但事實並非如此。為什麼？這是表格的轉儲。

CREATE TABLE public.mp_physical(
   id_mpp serial NOT NULL,
   id_comet_parts integer,
   "SPK_id" public.nonnegative_int,
   designation varchar(30),
   name varchar(100),
   prefix varchar,
   "is_NEO" bool,
   "H" double precision,
   "G" double precision,
   diameter public.nonnegative_double,
   extent varchar(30),
   extent_error public.nonnegative_double,
   geometric_albedo public.nonnegative_double,
   rot_per public.nonnegative_double,
   "GM" public.nonnegative_double,
   "BV" public.nonnegative_double,
   "UB" public.nonnegative_double,
   "spec_B" varchar(30),
   "spec_T" varchar(30),
   lca double precision,
   multiplicity public.nonnegative_int,
   polar_ang double precision,
   polar_slope_ang double precision,
   a double precision,
   b double precision,
   mass public.nonnegative_double,
   mp_type public.mp_type NOT NULL,
   periodic_number varchar(5),
   diameter_method_def varchar(200),
   discovery_info text,
   "H_sigma" public.nonnegative_double,
   "G_sigma" public.nonnegative_double,
   diameter_sigma public.nonnegative_double,
   geometric_albedo_sigma public.nonnegative_double,
   rot_per_sigma public.nonnegative_double,
   "GM_sigma" public.nonnegative_double,
   "BV_sigma" public.nonnegative_double,
   "UB_sigma" public.nonnegative_double,
   lca_sigma public.nonnegative_double,
   a_sigma public.nonnegative_double,
   b_sigma public.nonnegative_double,
   polar_ang_sigma public.nonnegative_double,
   mass_sigma public.nonnegative_double,
   CONSTRAINT "_C_id_ap" PRIMARY KEY (id_mpp)
);

CREATE INDEX "_I_name" ON  mp_physical  USING btree (name);
CREATE INDEX "_I_designation" ON mp_physical USING btree(mpp_designation);
CREATE INDEX "_I_periodic_number" ON mp_physical USING btree(periodic_number);
CREATE INDEX "_I_mp_type" ON mp_physical USING btree(mp_type);

和

 CREATE TEMPORARY TABLE "observations_optical_temp"(note_1,date,"RA","Dec",magnitude,band,id_observatory,id_mpp,"Dec_degree",observatory_code,periodic_number,mpp_designation,mp_type)
AS SELECT note_1,date,"RA","Dec",magnitude,band,id_observatory,id_mpp,"Dec_degree",'1'::varchar(3),'1'::varchar(8),'1'::varchar(30),'A'::public.mp_type FROM observations_optical;

CREATE TABLE observations_optical(
   id_obs_o bigint
   note_1 varchar,
   date timestamp NOT NULL,
   "RA" time NOT NULL,
   "Dec_degree" integer NOT NULL,
   "Dec" time NOT NULL,
   magnitude double precision,
   band varchar,
   id_observatory integer,
   id_mpp integer,
   CONSTRAINT "_PK_id_obs_o" PRIMARY KEY (id_obs_o)
);
CREATE INDEX "_I_temp_1" ON observations_optical_temp USING btree(mpp_designation);
CREATE INDEX "_I_temp_2" ON observations_optical_temp USING btree(periodic_number);
CREATE INDEX "_I_temp_3" ON observations_optical_temp USING btree(mp_type);

索引掃描和點陣圖索引掃描之間的選擇基本上取決於 Postgres 期望檢索的每個數據頁的行數 - 這取決於有關表中數據分佈的統計資訊和查詢謂詞的選擇性。並且由隨機記憶體訪問的預期成本 - 由成本設置定義，最突出的是random_page_cost和effective_cache_size.

如果 Postgres 期望在同一數據頁上找到足夠多的行，它會切換到點陣圖索引掃描，這對於這種物理數據分佈更有效。（如果檢索到大多數數據頁，那麼低順序掃描無論如何都會更快。）因此，即使您的兩個表的列和索引看起來都相同，您仍然可能會根據數據分佈和查詢謂詞的選擇性獲得這些不同的查詢計劃.

• 優化對一系列時間戳（一列）的查詢
• 帶有點陣圖索引掃描的查詢計劃中的“重新檢查條件：”行

但是你的桌子一開始就非常不同。mp_physical具有更寬的行，因此只有幾行位於同一數據頁上（預設為 8kb）。這非常有利於索引掃描，因為即使在檢索更高百分比的所有行時，點陣圖索引掃描也不會買太多（如果有的話）。

另外，請注意臨時表沒有被覆蓋autovacuum，因此不會被自動分析。您可能必須手動執行此操作以獲得準確的表統計資訊，以便查詢規劃器使用：

ANALYZE observations_optical_temp;

• 在 9.1 下是否仍建議使用正常 VACUUM ANALYZE？

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