Highly obscure and erratic(II)

hi ,
> select * from dbmedia, wdmedia a, wdmedia b
> where id = a.id and a.word='word1'
> and   id = b.id and b.word='word2'
> etc

well thanks for that..
but i really cannot understand y pgsql resorts to wierd behaviour..
like for this one :

==========================================================================
suryadb=# explain SELECT * FROM dbmedia d, wdmedia a1,wdmedia a2
          WHERE d.id = a1.id AND a1.word = 'word1' AND
          d.id=a2.id and a2.word='word2'
          and d.host like '144.16%' LIMIT 200;
NOTICE:  QUERY PLAN:
     Limit  (cost=5033.63..10547.45 rows=1 width=108)
     ->  Nested Loop  (cost=5033.63..10547.45 rows=1 width=108)
     ->  Hash Join  (cost=5033.63..10196.02 rows=83 width=32)
     ->  Index Scan using wdkmedia on wdmedia a1  (cost=0.00..5027.19 rows=2575 width=16)
     ->  Hash  (cost=5027.19..5027.19 rows=2575 width=16)
     ->  Index Scan using wdkmedia on wdmedia a2  (cost=0.00..5027.19 rows=2575 width=16)
->  Index Scan using indx2 on dbmedia d  (cost=0.00..4.21 rows=1 width=76)
============================================================================
                      
 clearly an index scan is done but for this...
==========================================================================
 suryadb=# explain SELECT * FROM dbmedia d, wdmedia a1
        WHERE d.id = a1.id AND a1.word = 'word1'
        and d.host like '144.16%' LIMIT 200;
 NOTICE:  QUERY PLAN:

 Limit  (cost=0.00..8811.42 rows=1 width=92)
   ->  Nested Loop  (cost=0.00..8811.42 rows=1 width=92)
           ->  Seq Scan on dbmedia d  (cost=0.00..3752.04 rows=1 width=76)
              ^^^^^^^^^^^^^^^
                   ->  Index Scan using wdkmedia on wdmedia a1  (cost=0.00..5027.19 rows=2575 width=16)
============================================================================

  y is a seq scan done here?
  hmm..perhaps cuz it might be thinking that '144.16%' has a high share?
  well but if the query is executed other way .. ie first Index scan on
  wdkmedia then Index scan on dbmedia is will be "inf" times fasters.
  I want it to first look in wdmedia and  then find the results by index
  scan which are present in dbmedia
  (which initially i was doing using  IN).
  Is there a way by which i can force it not to use seq scan?
  Will be grateful for any help

> Seems odd but it may work. But as a rule, joins are faster than subqueries
> and avoid IN, INTERSECT and UNION at all costs. Do you have a book on SQL
> around?
> --
--
------
Varun
If Bill Gates had a penny for every time Windows crashed......Oh wait, he does.