1

Using a pg_trgm index, I have the following query that runs in aprox 20 to 80 milliseconds :

SELECT count(*) from protein_seq p
WHERE  p.sequence LIKE '%SMYSFCKASFTQHNFYFFKN%';

the protein_seq table has 1255014 rows.

CREATE INDEX idx_protein_seq_sequence_gin  
ON protein_seq USING gin  (sequence gin_trgm_ops);

Is there a way to speed up this join :

SELECT * from protein_seq p, tmp_psm_seqs t
WHERE p.sequence LIKE '%'|| t.sequence || '%';

update

The above query is a simplification of the following query :

select
  pep.sequence, string_agg(DISTINCT g.symbol, ',')
FROM
  tmp_psm_seqs2 pep,
  protein_seq p,
  transcript_translation tt,
  transcript t,
  gene g,
  annotation a,
  assembly ass
WHERE
  p.sequence LIKE '%' || pep.sequence || '%' AND
  tt.protein_seq_id = p.protein_seq_id AND
  tt.transcript_id = t.transcript_id AND
  t.gene_id = g.gene_id AND
  g.annotation_id = a.annotation_id AND
  ass.assembly_id = a.assembly_id AND
  a.annotation_id not in (3,7) AND --bad annotations
  ass.species_id = 2
GROUP BY pep.sequence
HAVING  count(DISTINCT g.symbol) <> 1

The plans for the simplified query seems to use the index, but not for the second query.

plan for simplified query :

"QUERY PLAN"
"Nested Loop  (cost=49.81..1053426034.77 rows=1577312532 width=20)"
"  ->  Seq Scan on tmp_psm_seqs t  (cost=0.00..4125.55 rows=252655 width=16)"
"  ->  Bitmap Heap Scan on protein_seq p  (cost=49.81..4106.98 rows=6243 width=143)"
"        Recheck Cond: (sequence ~~ (('%'::text || t.sequence) || '%'::text))"
"        ->  Bitmap Index Scan on idx_protein_seq_sequence_gin  (cost=0.00..48.24 rows=6243 width=0)"
"              Index Cond: (sequence ~~ (('%'::text || t.sequence) || '%'::text))"

plan for "real" query :

"QUERY PLAN"
"GroupAggregate  (cost=1010191937.07..1012621800.28 rows=252655 width=27)"
"  Group Key: pep.sequence"
"  Filter: (count(DISTINCT g.symbol) <> 1)"
"  ->  Sort  (cost=1010191937.07..1010798455.42 rows=242607338 width=27)"
"        Sort Key: pep.sequence"
"        ->  Nested Loop  (cost=1.84..970599383.45 rows=242607338 width=27)"
"              Join Filter: (p.sequence ~~ (('%'::text || pep.sequence) || '%'::text))"
"              ->  Seq Scan on tmp_psm_seqs pep  (cost=0.00..4125.55 rows=252655 width=16)"
"              ->  Materialize  (cost=1.84..168095.41 rows=192046 width=150)"
"                    ->  Nested Loop  (cost=1.84..167135.18 rows=192046 width=150)"
"                          ->  Nested Loop  (cost=1.41..62995.27 rows=192046 width=19)"
"                                ->  Nested Loop  (cost=0.98..14472.70 rows=41038 width=15)"
"                                      ->  Nested Loop  (cost=0.56..1544.25 rows=18142 width=15)"
"                                            ->  Nested Loop  (cost=0.14..13.52 rows=1 width=4)"
"                                                  Join Filter: (a.assembly_id = ass.assembly_id)"
"                                                  ->  Index Scan using annotation_annotation_id_pkey on annotation a  (cost=0.14..12.31 rows=8 width=12)"
"                                                        Filter: (annotation_id <> ALL ('{3,7}'::integer[]))"
"                                                  ->  Materialize  (cost=0.00..1.09 rows=1 width=4)"
"                                                        ->  Seq Scan on assembly ass  (cost=0.00..1.09 rows=1 width=4)"
"                                                              Filter: (species_id = 2)"
"                                            ->  Index Scan using gene_annotation_id on gene g  (cost=0.42..1303.96 rows=22677 width=23)"
"                                                  Index Cond: (annotation_id = a.annotation_id)"
"                                      ->  Index Scan using transcript_gene_id on transcript t  (cost=0.42..0.65 rows=6 width=12)"
"                                            Index Cond: (gene_id = g.gene_id)"
"                                ->  Index Scan using idx_transcript_translation_transcript_id on transcript_translation tt  (cost=0.43..1.02 rows=16 width=16)"
"                                      Index Cond: (transcript_id = t.transcript_id)"
"                          ->  Index Scan using protein_seq_protein_seq_id_pkey on protein_seq p  (cost=0.43..0.53 rows=1 width=143)"
"                                Index Cond: (protein_seq_id = tt.protein_seq_id)"
1
  • Did you ANALYZE your tables after you created an index? Jun 16, 2016 at 18:05

3 Answers 3

3

I highly recommend re-writing your query using proper join syntax:

select
  pep.sequence, string_agg(DISTINCT g.symbol, ',')
FROM tmp_psm_seqs2 pep
JOIN protein_seq p ON p.sequence LIKE '%' || pep.sequence || '%'
JOIN transcript_translation tt ON tt.protein_seq_id = p.protein_seq_id
JOIN transcript t ON tt.transcript_id = t.transcript_id
JOIN gene g ON t.gene_id = g.gene_id
JOIN annotation a ON g.annotation_id = a.annotation_id
JOIN assembly ass ON ass.assembly_id = a.assembly_id
WHERE
  a.annotation_id not in (3,7) AND --bad annotations
  ass.species_id = 2
GROUP BY pep.sequence
HAVING  count(DISTINCT g.symbol) <> 1

Based off this, I can see that the query looks normal except for the horrendous JOIN on a LIKE. To me, it looks like the query planner is either getting confused on the number of matches it EXPECTS to get from that JOIN (and thus plans a sequential scan), or reporting accurately and your query is potentially doing some sort of cartesian product.

I'm curious to see what it reports with this query (I do not know if results will be the same as original query): (it just separates the LIKE join from all the others)

WITH proteins AS (
  select
    p.sequence, string_agg(DISTINCT g.symbol, ',') AS symbols
  FROM protein_seq p
  JOIN transcript_translation tt ON tt.protein_seq_id = p.protein_seq_id
  JOIN transcript t ON tt.transcript_id = t.transcript_id
  JOIN gene g ON t.gene_id = g.gene_id
  JOIN annotation a ON g.annotation_id = a.annotation_id
  JOIN assembly ass ON ass.assembly_id = a.assembly_id
  WHERE
    a.annotation_id not in (3,7) AND --bad annotations
    ass.species_id = 2
  GROUP BY p.sequence
  HAVING count(DISTINCT g.symbol) <> 1
) SELECT
  pep.sequence, p.symbols
FROM tmp_psm_seqs2 pep
JOIN proteins p ON p.sequence LIKE '%' || pep.sequence || '%';
2

The plan I see when I reconstruct your example is that it does a nested loop around a sequential scan on tmp_psm_seqs and a bitmap scan, using the index you created, on protein_seq. That seems like a pretty good plan to me.

1
  • you're right, this query is a simplified version of a more complex one (see the update), for the "real" query, the index doesn't seem to be used.
    – Max L.
    Jun 16, 2016 at 19:03
0

The planner can't get a good estimate of how selective your join is going to be, and so can't make good decisions about it.

Can you force it to do the join you want with a CTE, and then join that to the rest of the tables?

WITH t as (SELECT * from protein_seq p, tmp_psm_seqs t
    WHERE p.sequence LIKE '%'|| t.sequence || '%'
) 
select ... from t join ....

Another option would be to make the LIKE operator look much more expensive, which should drive it away from using it as a filter in a cartesian join. As superuser, you could do something like:

update pg_proc set procost=100 where proname = 'textlike';

Note that this will have database-wide effects.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.