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.
