On Tue, 18 Jul 2000, Steve Heaven wrote:
> At 11:44 17/07/00 -0700, Stephan Szabo wrote:
> >First off, is that query really what you want?
> >main.* is probably adding an extra join with main (see the explain output --
> >it appears to be doing two joins against main, one as m and one as main).
> >
> >Try the query as
> >select m.* from main m, subset_table s where m.stockno=s.stockno and
> >m.descrip ~ 'SEARCHTERM';
> >
> >and see what it gives you then.
> >
>
> Its different, but it still does the 'wrong' scan first and even stranger
> now it doesnt do an indexed scan on subset_table:
Well, as Tom said, if you're actually hitting alot of the rows in the
subset table, index scan is slower. Plus, I think index scan is only
an option on anchored regexps (so what SEARCHTERM is will affect it).
If you think that the data is not similar to what it is saying (ie, that
you're getting alot of rows), you may not have done a VACUUM ANALYZE
recently and the stats could be out of wack, or you might have a very
common value that is throwing the optimizer off.