datetime vs date + indexed smallint (PostgreSQL) 的搜尋效率

語境

我們將天氣預報儲存為多波段柵格（使用 Postgres 和 PostGIS）。每個預測都由其日期和時間（0:00、6:00 或 12:00）標識。我們正在查看大約 20 年的數據。除了出於研究目的而進行的分析（速度不是最重要的）之外，還將有一個線上儀表板，將在地圖上顯示最新的預測。

該表可以具有以下列： datetime, metadata_id, raster_data

我預見了兩個主要案例：（1）要麼獲取最新的預測，要麼（2）選擇一個過去的預測。查詢可能如下所示：

1. SELECT datetime, metadata_id, raster_data FROM myTable ORDER BY datetime DESC LIMIT 1;
2. SELECT datetime, metadata_id, raster_data FROM myTable WHERE datetime= '2015-10-12 12:00:00'::timestamp;

更廣泛的背景

上述情況實際上適用於 6 個不同的表，只是它們的數據來自不同的來源，表示的物理變數不同。因此，我正在考慮一個包含以下列的表： datetime, dataSource_physVar, metadata_id, raster_data

不同的來源確實有不同的柵格範圍，所以我考慮使用子表。

問題 1

鑑於有限的案例，使用單個 datetime 欄位，還是使用帶有索引 smallint 欄位的日期欄位更好？

問題2

鑑於數據量（365320 年 = 22k 行），是否還值得擔心效率？

我正在為您的兩個問題和您的索引的其他注意事項提出建議。

1

我會將時間戳保留為一個欄位，將其分為兩個單獨的日期欄位和一個小時的 smallint 將每行減少 2 個字節的空間（即 43 MB 與您的 22k 行估計）必須比較兩個欄位的日期/time 似乎不值得小小的空間增益。

2

與某些數據庫相比，22k 行可能並不多，但如果您要挑選單個事件，那麼您仍然希望它盡可能快。從堆中查找特定值需要進行全表掃描，這可能需要一些時間，如果它已編入索引，那麼您將進行索引查找，這意味著您可以更快地找到記錄。

額外的

如果數據按位置分為不同的行（例如，‘2015/10/13 00:00’、‘London’、‘sunny’），那麼我建議在時間戳和位置之間使用複合鍵

我不相信是這樣，聽起來數據將儲存在一個長行中，所以（日期時間時間戳，倫敦（數據），巴黎（數據）），為此只是索引時間戳欄位，作為索引就足夠了

希望有幫助

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