使用索引查詢域名表以獲取匹配的頂級域

我需要一個表，其中包含一個帶有域+後綴的單列，看起來像這樣：

我希望能夠使用完整的域名 ( photos.api.google.com, www.ynet.co.il, bla.bla.dd.cc.360.cn) 查詢此表，以便從表中獲取匹配項。

我設法使它像這樣工作：

create table sites(domain_and_suffix text);

insert into sites values ('google com'),('ynet co il'),('360 cn');


with s(d) as (
   VALUES
       (to_tsvector('ddd ddd amazon com')),
       (to_tsvector('www ynet co il')),
       (to_tsvector('ccc aaa google com')),
       (to_tsvector('bla bla dd cc 360 cn'))
)
select *
from s inner join sites on (s.d @@ plainto_tsquery(sites.domain_and_suffix));

https://www.db-fiddle.com/f/tG7Y5xUXXGgEqyMo7RbuxN/2

我的問題是我希望能夠使用索引進行此搜尋。我無法通過後綴匹配、正則表達式、使用基數樹的 SP-GIST 或任何其他方式找到合適的方法，因為我需要將“後綴”儲存在表中並使用要匹配的值進行查詢。

因此，例如，如果我想使用基數樹（從這裡）採用 SP-GIST 的方法，我不能因為全文被用於查詢並且模式被儲存在 DB 表中。

我正在使用 PostgreSQL 12。

一種方式：生成所有子域，頂層向下，取第一個匹配：

WITH cte AS (
  SELECT ord, string_agg(label, '.') OVER (ORDER BY ord ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS subdomain
  FROM   unnest(string_to_array('bla.bla.dd.cc.360.cn', '.')) WITH ORDINALITY AS x(label, ord)
  ORDER  BY ord DESC
  OFFSET 1  -- skip tld
  )
SELECT s.*
FROM   cte c
CROSS  JOIN LATERAL (
  SELECT domain_and_suffix FROM sites s
  WHERE  s.domain_and_suffix = c.subdomain
  ) s
ORDER  BY c.ord DESC
LIMIT  1;  -- take only top-level match (?)

db<>在這裡擺弄

只需要一個簡單的 btree 索引sites.domain_and_suffix。UNIQUE或PRIMARY KEY約束隱式domain_and_suffix創建該索引。如果表VACCUM足夠，您將看到閃電般快速的 index-only scans。

CTE 是生成一組子域的一種方式。在 Postgres 14 或更高版本中，使用string_to_table()代替unnest(string_to_array()).

如果你經常使用這個查詢，我建議為元件編寫一個更快的自定義集合返回函式。可能看起來像這樣：

CREATE OR REPLACE FUNCTION f_subdomains(_domain text)
 RETURNS TABLE(subdomain text)
 LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
DECLARE
  _arr text[] := string_to_array($1, '.');
  _idx int    := cardinality(_arr);
BEGIN
  subdomain := _arr[_idx];
  WHILE _idx > 1 LOOP
     _idx := _idx - 1;
     subdomain := _arr[_idx] || '.' || subdomain; 
     RETURN NEXT;
  END LOOP;
END
$func$;

排除上面的 TLD。

那麼查詢很簡單：

SELECT s.*
FROM   f_subdomains('bla.bla.dd.cc.360.cn') d
CROSS  JOIN LATERAL (
  SELECT domain_and_suffix FROM sites s
  WHERE  s.domain_and_suffix = d.subdomain
  ) s
LIMIT  1;

db<>在這裡擺弄

依賴於從集合返回函式返回的行的順序。哪個有效。如果您不信任它（不是嚴格的標準 SQL，沒有ORDER BY），請添加並添加一個像上面這樣WITH ORDINALITY的外部。ORDER BY

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