Re: abnormally long time in performing a two-table join

Chris Mungall <cjm@fruitfly.org> writes:
> Nested Loop  (cost=0.00..197011.39 rows=223 width=8) (actual time=16744.92..44572.00 rows=15 loops=1)
>   ->  Index Scan using seqfeature_pkey on seqfeature  (cost=0.00..61715.62 rows=44674 width=4) (actual
time=0.29..14669.06rows=100030 loops=1) 
>   ->  Index Scan using sfqv_idx1 on sfqv  (cost=0.00..3.02 rows=1 width=4) (actual time=0.29..0.29 rows=0
loops=100030)

Odd ... I'm surprised it doesn't choose a hash join.  What do you get if
you try it with "set enable_nestloop = off" ?

> CREATE INDEX sfqv_idx4 ON sfqv USING btree (seqfeature_id, qualifier_value);

> I would have thought sfqv_idx4 would be useful in this particular query?

You'd have to write

select ... where qualifier_value = 'BRCA1' and
seqfeature.seqfeature_key_id = 15 and
sfqv.seqfeature_key_id = 15

to get it to consider that index.  You know and I know that the join
should imply sfqv.seqfeature_key_id = 15, but the planner doesn't.

            regards, tom lane