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