Re: ordering of 'where' sub clauses

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:

explain select m.* from main m, subset_table s
where m.stockno=s.stockno AND m.descrip ~ 'SEARCHTERM';
NOTICE: QUERY PLAN:

Hash Join (cost=118431.88 rows=714797 width=172)
-> Seq Scan on main m (cost=79300.27 rows=714796 width=160)
-> Hash (cost=8121.17 rows=203793 width=12)
-> Seq Scan on subset_table s (cost=8121.17 rows=203793 width=12)

I'm confused !
Steve

--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk