select e.id, (select count(id) from imgitem ii where ii.tabid = e.id and ii.tab = 'esp') as imgs,
e.ano, e.mes, e.dia, cast(cast(e.ano as varchar(4))||'-'||right('0'||cast(e.mes as varchar(2)),2)||'-'|| right('0'||cast(e.dia as varchar(2)),2) as varchar(10)) as data,
pl.pltag, e.inpa, e.det, d.ano anodet, coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')' determinador, d.tax, coalesce(v.val,v.valf)||' '||vu.unit as altura,
coalesce(v1.val,v1.valf)||' '||vu1.unit as DAP, d.fam, tf.nome família, d.gen, tg.nome gênero, d.sp, ts.nome espécie, d.inf, e.loc, l.nome localidade, e.lat, e.lon
from esp e
left join det d on e.det = d.id
left join tax tf on d.fam = tf.oldfam
left join tax tg on d.gen = tg.oldgen
left join tax ts on d.sp = ts.oldsp
left join tax ti on d.inf = ti.oldinf
left join loc l on e.loc = l.id
left join pess p on p.id = d.detby
left join var v on v.esp = e.id and v.key = 265
left join varunit vu on vu.id = v.unit
left join var v1 on v1.esp = e.id and v1.key = 264
left join varunit vu1 on vu1.id = v1.unit
left join pl on pl.id = e.pl
WHERE unaccent(TEXT(coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')')) ilike unaccent('%vicen%')
从esp表中的总计9250中检索1129行需要430ms.
如果我将搜索项从％vicen％更改为％vicent％(添加’t’),则需要431ms来检索相同的1129行.
按搜索列排序,升序和降序,我看到所有1129行在两种情况下都具有完全相同的名称.
现在很奇怪：如果我将搜索词从％vicent％更改为％vicenti％(添加’i’),现在需要难以置信的24.4秒来检索相同的1129行！
搜索到的术语总是在第一个合并中,即合并(p.abrev,”).我希望查询运行得更慢或更快,具体取决于搜索字符串的大小,但不是那么多！任何人都知道发生了什么事吗？
EXPLAIN ANALYZE的结果(这里将超过30k字符限制)：
对于％vicen％：http://explain.depesz.com/s/2XF
对于％vicenti％：http://explain.depesz.com/s/dEc6
为什么？原因是这样的：
快速查询：
-> Hash Left Join (cost=1378.60..2467.48 rows=15 width=79) (actual time=41.759..85.037 rows=1129 loops=1)
...
Filter: (unaccent(((((COALESCE(p.abrev, ''::character varying))::text || ' ('::text) || (COALESCE(p.prenome, ''::character varying))::text) || ')'::text)) ~~* (...)
慢查询：
-> Hash Left Join (cost=1378.60..2467.48 rows=1 width=79) (actual time=35.084..80.209 rows=1129 loops=1)
...
Filter: (unaccent(((((COALESCE(p.abrev, ''::character varying))::text || ' ('::text) || (COALESCE(p.prenome, ''::character varying))::text) || ')'::text)) ~~* unacc (...)
通过另一个角色扩展搜索模式会导致Postgres假设更少的匹配. (通常情况下,这是一个合理的估计.)Postgres显然没有足够精确的统计数据(没有,实际上,继续阅读)以期望获得相同数量的命中.
这导致切换到不同的查询计划,这对于实际的命中行数= 1129来说甚至更不理想.
解
假设当前Postgres 9.5尚未宣布.
改善这种情况的一种方法是在谓词中的表达式上创建表达式索引.这使得Postgres收集实际表达式的统计信息,即使索引本身未用于查询,也可以帮助查询.没有索引,根本没有表达式的统计信息.如果做得好,索引可以用于查询,那就更好了.但是您当前的表达式存在多个问题：
unaccent(TEXT(coalesce(p.abrev,”)||'(‘|| coalesce(p.prenome,”)||’)’))ilike unaccent(‘％vicen％’)
根据有关未公开表定义的一些假设,考虑此更新的查询：
SELECT e.id
, (SELECT count(*) FROM imgitem
WHERE tabid = e.id AND tab = 'esp') AS imgs -- count(*) is faster
, e.ano, e.mes, e.dia
, e.ano::text || to_char(e.mes2, 'FM"-"00')
|| to_char(e.dia, 'FM"-"00') AS data
, pl.pltag, e.inpa, e.det, d.ano anodet
, format('%s (%s)', p.abrev, p.prenome) AS determinador
, d.tax
, coalesce(v.val,v.valf) || ' ' || vu.unit AS altura
, coalesce(v1.val,v1.valf) || ' ' || vu1.unit AS dap
, d.fam, tf.nome família, d.gen, tg.nome AS gênero, d.sp
, ts.nome AS espécie, d.inf, e.loc, l.nome localidade, e.lat, e.lon
FROM pess p -- reorder!
JOIN det d ON d.detby = p.id -- INNER JOIN !
LEFT JOIN tax tf ON tf.oldfam = d.fam
LEFT JOIN tax tg ON tg.oldgen = d.gen
LEFT JOIN tax ts ON ts.oldsp = d.sp
LEFT JOIN tax ti ON ti.oldinf = d.inf
-- unused, see @joop's comment
LEFT JOIN esp e ON e.det = d.id
LEFT JOIN loc l ON l.id = e.loc
LEFT JOIN var v ON v.esp = e.id AND v.key = 265
LEFT JOIN varunit vu ON vu.id = v.unit
LEFT JOIN var v1 ON v1.esp = e.id AND v1.key = 264
LEFT JOIN varunit vu1 ON vu1.id = v1.unit
LEFT JOIN pl ON pl.id = e.pl
WHERE f_unaccent(p.abrev) ILIKE f_unaccent('%' || 'vicenti' || '%') OR
f_unaccent(p.prenome) ILIKE f_unaccent('%' || 'vicenti' || '%');
主要观点
为什么f_unaccent()？因为unaccent()无法编入索引.读这个：
> Does PostgreSQL support “accent insensitive” collations?
我使用那里概述的功能允许以下(推荐！)多列功能trigram GIN索引：
CREATE INDEX pess_unaccent_nome_trgm_idx ON pess USING gin (f_unaccent(pess) gin_trgm_ops, f_unaccent(prenome) gin_trgm_ops);
如果您不熟悉trigram索引,请首先阅读：
> PostgreSQL LIKE query performance variations
可能：
> Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
一定要运行最新版本的Postgres(目前为9.5). GIN指数有了实质性的改进.您将对pg_trgm 1.2的改进感兴趣,计划与即将推出的Postgres 9.6一起发布：
> Trigram search gets much slower as search string gets longer
准备语句是使用参数执行查询的常用方法(尤其是使用来自用户输入的文本). Postgres必须找到一个最适合任何给定参数的计划.将通配符作为常量添加到搜索词中,如下所示：
f_unaccent(p.abrev) ILIKE f_unaccent('%' || 'vicenti' || '%')
(‘vicenti’将被替换为参数.)因此,Postgres知道我们正在处理一种既不左右也不是左右的模式 – 这将允许不同的策略.相关答案详情如下：
> Performance impact of empty LIKE in a prepared statement
或者可能为每个搜索词重新规划查询(可能在函数中使用动态SQL).但要确保计划时间不会占用任何可能的性能提升.
pess中列的WHERE条件与LEFT JOIN相矛盾. Postgres被迫将其转换为INNER JOIN.更糟糕的是连接在连接树中来得晚了.而且由于Postgres无法重新排序您的联接(见下文),这可能会变得非常昂贵.将表移动到FROM子句中的第一个位置以尽早消除行. LEFT JOIN之后根据定义不会消除任何行.但是,有了这么多表,移动可能会将行数乘以结尾的连接非常重要.
你正在加入13张桌子,其中12张桌子有LEFT JOIN,剩下12张！可能的组合 – 或11！ * 2！如果我们考虑一个LEFT JOIN,那真的是一个内部联接.对于Postgres而言,这对于评估最佳查询计划的所有可能排列来说太多了.阅读join_collapse_limit：
> Sample Query to show Cardinality estimation error in PostgreSQL
> SQL INNER JOIN over multiple tables equal to WHERE syntax
join_collapse_limit的默认设置为8,这意味着Postgres不会尝试重新排序FROM子句中的表,并且表的顺序是相关的.
解决这个问题的一种方法是将性能关键部分拆分为像@joop commented这样的CTE.不要将join_collapse_limit设置得更高或者涉及许多连接表的查询计划的时间会恶化.
关于您的连接日期命名数据：
cast(cast(e.ano as varchar(4))||’ – ‘|| right(‘0’|| cast(e.mes as varchar(2)),2)||’ – ‘|| right( ‘0’|| cast(e.dia as varchar(2)),2)as varchar(10))作为数据
假设您使用三个数字列构建年,月和日(定义为NOT NULL),请使用以下代码：
e.ano::text || to_char(e.mes2, 'FM"-"00')
|| to_char(e.dia, 'FM"-"00') AS data
关于FM模板模式修饰符：
> Check for integer in string array
但实际上,您应该将日期存储为数据类型date.
还简化了：
format('%s (%s)', p.abrev, p.prenome) AS determinador
不会使查询更快,但它更清洁.见format().
首先,所有通常的性能优化建议都适用：
> Keep PostgreSQL from sometimes choosing a bad query plan
如果你能做到这一切,你应该会看到所有模式的查询速度要快得多.