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

Chris Mungall <cjm(at)fruitfly(dot)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.06 rows=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