Postgres中有效的反向前缀搜索

发布于 2025-01-30 09:57:18 字数 399 浏览 7 评论 0原文

假设一张100K加拿大邮政代码前缀具有人口尺寸：

区域代码	人口
H2	10,000,000
H2Z	100,000
H2K	50,000
H2Z 1G9	500

给定完整的邮政编码，例如“ H2Z 1G9”，我需要返回每个行的区域代码，输入。由于某种原因，我需要做很多事情，因此我需要有效的索引。

我应该怎么做？

原文

Assume a table of 100K Canadian postal code prefixes with population sizes:

Region code	Population
H2	10,000,000
H2Z	100,000
H2K	50,000
H2Z 1G9	500

Given a full length postal code, e.g. "H2Z 1G9" I need to return every row whose region code is a prefix of the input. For some reason I need to do this a lot, so I need effective indexing.

How should I go about this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泡沫很甜 2025-02-06 09:57:18

您可以使用这样的函数：

CREATE TABLE tab (
   region text PRIMARY KEY,
   population bigint NOT NULL
);

CREATE FUNCTION find_match (t text) RETURNS SETOF tab
   LANGUAGE plpgsql AS
$DECLARE
   s text;
BEGIN
   FOR s IN SELECT substr(t, 1, n)
            FROM generate_series(1, length(t)) AS s(n)
   LOOP
      RETURN QUERY SELECT * FROM tab
                   WHERE region = s;
   END LOOP;
END;$;

You could use a function like this:

CREATE TABLE tab (
   region text PRIMARY KEY,
   population bigint NOT NULL
);

CREATE FUNCTION find_match (t text) RETURNS SETOF tab
   LANGUAGE plpgsql AS
$DECLARE
   s text;
BEGIN
   FOR s IN SELECT substr(t, 1, n)
            FROM generate_series(1, length(t)) AS s(n)
   LOOP
      RETURN QUERY SELECT * FROM tab
                   WHERE region = s;
   END LOOP;
END;$;

回复收藏 0 原文

清引 2025-02-06 09:57:18

这样的工作会有效吗？您可以检查说明分析以确保其使用表上的PK：

with in_postal_code as (
  select distinct rtrim((left('H2Z 1G9', gs.n))) as postal_prefix
    from generate_series(1, 7) as gs(n)
)
select cp.*
  from in_postal_code i
       join postal_code_population cp
         on cp."Region Code" = i.postal_prefix;

Would something like this work efficiently? You can check explain analyze to ensure it uses the PK on your table:

with in_postal_code as (
  select distinct rtrim((left('H2Z 1G9', gs.n))) as postal_prefix
    from generate_series(1, 7) as gs(n)
)
select cp.*
  from in_postal_code i
       join postal_code_population cp
         on cp."Region Code" = i.postal_prefix;

回复收藏 0 原文

~没有更多了~