URL文本字段的查询优化

Question

我正在编码一个域名，网址和正则表达式(如SquidGuard)过滤器为Squid使用eCAP协议，我想存储所有的域名到一个postgresql数据库。问题是，当我用like，example进行搜索时：

SELECT website_groups.id,
       "name",
       description
FROM website_domains
   JOIN website_groups ON website_group_id = website_groups.id
WHERE (website_domains.domain = 'google.com'
    OR website_domains.domain LIKE '%.google.com')

对1'605'923个元组的查询有490ms的延迟，但这不利于对squid代理的所有请求。

我的问题是如何优化PostgreSQL以提高查询速度，或者我需要使用NoSQL数据库(我使用MongoDB进行测试，使用较少的数据在609ms内完成查询)。

我尝试过FullText搜索，但它有英文标记化，数据是URL (www.google.com/query?data1=3)、域(bing.com)和正则表达式(.*.cu)。

Answer 1

您可以尝试为反向域字符串创建一列，并在其上创建索引：

ALTER TABLE website_domains ADD reverse_domain VARCHAR(100);
UPDATE website_domains SET reverse_domain = REVERSE(domain);
CREATE INDEX reverse_domain_index ON 
    website_domains (reverse_domain varchar_pattern_ops);

如果可能，varchar_pattern_ops允许LIKE使用此索引。

前缀搜索也是通过颠倒模式完成的：

... OR website_domains.reverse_domain LIKE REVERSE('%.google.com')

您可能可以避免使用计算索引的额外列

CREATE INDEX reverse_domain_index ON 
    website_domains (REVERSE(domain) varchar_pattern_ops);

和下面的子句：

.. OR REVERSE(website_domains.domain) LIKE REVERSE('%.google.com')

但你应该试试。