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

Chris Mungall <cjm(at)fruitfly(dot)org> writes:
> On Sun, 11 Aug 2002, Tom Lane wrote:
>> Odd ... I'm surprised it doesn't choose a hash join. What do you get if
>> you try it with "set enable_nestloop = off" ?

> Much better!

> omicia29=# set enable_nestloop = off;
> SET VARIABLE
> omicia29=# explain analyze select seqfeature_id from seqfeature NATURAL
> JOIN sfqv where qualifier_value = 'BRCA1' and seqfeature.seqfeature_key_id
> = 15;
> NOTICE: QUERY PLAN:

> Hash Join (cost=55921.31..223860.67 rows=778 width=8) (actual
> time=4249.63..4259.47 rows=15 loops=1)
> -> Index Scan using sfqv_idx3 on sfqv (cost=0.00..167411.84 rows=41423
> width=4) (actual time=38.05..44.50 rows=110 loops=1)
> -> Hash (cost=51056.43..51056.43 rows=49453 width=4) (actual
> time=4211.15..4211.15 rows=0 loops=1)
> -> Seq Scan on seqfeature (cost=0.00..51056.43 rows=49453
> width=4) (actual time=0.14..3974.12 rows=100030 loops=1)
> Total runtime: 4259.67 msec

Hmm. It looks like the reason the planner doesn't like this plan is
that it's vastly overestimating the number of rows it will get from
indexscanning sfqv_idx3 for qualifier_value = 'BRCA1' (viz, 41423
rather than the true 110). This is a statistical failure.

What do you get from
select * from pg_stats where tablename = 'sfqv'
?

You might try increasing the statistics target for the qualifier_value
column --- I'm guessing that you need more resolution in the stats to
deal correctly with low-probability data. Try
ALTER TABLE sfqv ALTER COLUMN qualifier_value SET STATISTICS 100;
ANALYZE sfqv; -- to recompute stats
then see how the EXPLAIN results change. (The default stats target is
10; 100 might be more than you need, or perhaps not.)

> however I'm not sure what the implications of turning nestloop off
> altogether are - maybe i can hardcode it just for this query

It'd be best not to. I'd counsel seeing if more stats help, first.

regards, tom lane