I have the following query in PostgreSQL (9.5.1):
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%')
It takes 430ms to retrieve 1129 rows from a total of 9250 in esp table.
If I change the search term from %vicen% to %vicent% (adding a 't'), it takes 431ms to retrieve the same 1129 rows.
Ordering by the search column, ascending and descending, I see that all 1129 rows have exactly the same name in both cases.
Now the strange: if I change the search term from %vicent% to %vicenti% (adding an 'i'), now it takes unbelievable 24.4 seconds to retrieve the same 1129 rows!
The searched term is always in the first coalesce, i.e. coalesce(p.abrev,''). I expect the query to run slower or faster, depending on the size of the searched string, but not that much!! Anyone has any idea of what's going on?
Results of EXPLAIN ANALYZE (would exceed the 30k character limit here):
For %vicen%: http://explain.depesz.com/s/2XF
For %vicenti%: http://explain.depesz.com/s/dEc6
Fast query:
-> 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)) ~~* (...)
Slow query:
-> 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 (...)
Extending the search pattern by another character causes Postgres to assume yet fewer hits. (Typically, this is a reasonable estimate.) Postgres obviously does not have precise enough statistics (none, actually, see below) to expect the same number of hits that you really get.
This causes a switch to a different query plan, which is even less optimal for the actual number of hits rows=1129.
One way to improve the situation is to create an expression index on the expression in the predicate. This makes Postgres gather statistics for the actual expression, which can help the query even if the index itself is not used for the query. Without the index, there are no statistics for the expression at all. And if done right the index can be used for the query, that's even much better. But there are multiple problems with your current expression:
unaccent(TEXT(coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')')) ilike unaccent('%vicen%')
Consider this updated query, based on some assumptions about your undisclosed table definitions:
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' || '%');Why f_unaccent()? Because unaccent() can't be indexed. Read here:
I used the function outlined there to allow the following (recommended!) multicolumn functional trigram GIN index:
CREATE INDEX pess_unaccent_nome_trgm_idx ON pess
USING gin (f_unaccent(pess) gin_trgm_ops, f_unaccent(prenome) gin_trgm_ops);
If you are not familiar with trigram indexes, read this first:
And possibly:
Be sure to run the latest version of Postgres (9.5 at the time of writing). There have been substantial improvements to GIN indexes. And you'll be interested in improvements in pg_trgm 1.2, scheduled to be released with the upcoming Postgres 9.6:
Prepared statements are a common way to execute queries with parameters (especially with text from user input). Postgres has to find a plan that works best for any given parameter. Add wildcards as constants to the to the search term like this:
f_unaccent(p.abrev) ILIKE f_unaccent('%' || 'vicenti' || '%')('vicenti' would be replaced with a parameter.) So Postgres knows we are dealing with a pattern that is neither anchored left nor right - which would allow different strategies. Related answer with more details:
Or maybe re-plan the query for every search term (possibly using dynamic SQL in a function). But make sure planning time isn't eating any possible performance gain.
The WHERE condition on columns in pess contradicts the LEFT JOININNER JOIN. Unfortunately, the join comes late in the join tree. And since Postgres cannot reorder your joins (see below), that can become very expensive. Move the table to the first position in the FROM clause to eliminate rows early. Following LEFT JOINs do not eliminate any rows by definition. But with that many tables it is important to move joins that might multiply rows to the end.
You are joining 13 tables, 12 of them with LEFT JOIN which leaves 12! possible combinations - or 11! * 2! if we take the one LEFT JOIN into account that's really an INNER JOIN. That's too many for Postgres to evaluate all possible permutations for the best query plan. Read about join_collapse_limit:
The default setting for join_collapse_limit is 8, which means that Postgres won't try to reorder tables in your FROM clause and the order of tables becomes relevant.
One way to work around this would be to split the performance-critical part into a CTE like @joop commented. Don't set join_collapse_limit much higher or times for query planning involving many joined tables will deteriorate.
About your concatenated date named data:
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
Assuming you build from three numeric columns for year, month and day, which are defined NOT NULL, use this instead:
e.ano::text || to_char(e.mes2, 'FM"-"00')
|| to_char(e.dia, 'FM"-"00') AS data
About the FM template pattern modifier:
But really, you should store the date as data type date to begin with.
Also simplified:
format('%s (%s)', p.abrev, p.prenome) AS determinador
Won't make the query faster, but it's much cleaner. See format().
First things last, all the usual advice for performance optimization applies:
If you get all of this right, you should see much faster queries for all patterns.