Can someone suggest how to improve the following query, so as to make the
LIKE section operate on the results found by the = one?
SELECT ndict.url_id,ndict.intag
FROM ndict,url
WHERE ndict.word_id=-720551816
AND url.rec_id=ndict.url_id
AND ((url.url || '') LIKE '%http://www.postgresql.org/%%')
ndict.word_id=-720551816 returns 5895 records
((url.url || '') LIKE '%http://www.postgresql.org/%%')
- returns 138k records
explain shows:
NOTICE: QUERY PLAN:
Hash Join (cost=10163.01..26647.09 rows=42 width=12)
-> Index Scan using n_word on ndict (cost=0.00..16299.52 rows=4180 width=8)
-> Hash (cost=10159.53..10159.53 rows=1390 width=4)
-> Seq Scan on url (cost=0.00..10159.53 rows=1390 width=4)
EXPLAIN
I'm figuring that if I can somehow get the query (using subselects,
maybe?), to have the LIKE part of the query work only on the 6k records
returned by the "=" part of it, the overall results should be faster ...
Possible?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
