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