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

On Sun, 11 Aug 2002, Chris Mungall wrote:

> > 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.)
>
> Hmm, it still doesn't force a hash join
>
> i've included the output from the same query again, twice - first with
> nestloop disabled, then with it enabled.
>

[SNIP]

>
>
> > > 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.
>
> Ok, I'm going to try upping it from 100....

I tried 1000, it still insists on doing a nested loop. any other
suggestions for forcing a hash join over a nested loop?

> >             regards, tom lane
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>