pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[7.0.3] optimizing a LIKE query ...
2+ messages / 2 participants
[nested] [flat]
* [7.0.3] optimizing a LIKE query ...
@ 2001-04-04 14:59 The Hermit Hacker <scrappy@hub.org>
0 siblings, 1 reply; 2+ messages in thread
From: The Hermit Hacker @ 2001-04-04 14:59 UTC (permalink / raw)
To: pgsql-sql
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: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [7.0.3] optimizing a LIKE query ...
@ 2001-04-04 23:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: The Hermit Hacker <scrappy@hub.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2001-04-04 23:06 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql
The Hermit Hacker <scrappy@hub.org> writes:
> 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 ...
In 7.0.* I think the only way to do that is to select into a temp table
and then apply the LIKE while selecting from the temp table.
In 7.1 you could possibly force the order by using a subselect (although
offhand I think the planner might be smart enough to see through that,
and do what it thinks is right anyway). The real problem is the planner
thinks that LIKE '%http://www.postgresql.org/%%'; is really selective; it
has no idea that most of your table mentions pgsql.org URLs :-(. We
need better statistics to fix this properly. (On my list for 7.2.)
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-04-04 23:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-04 14:59 [7.0.3] optimizing a LIKE query ... The Hermit Hacker <scrappy@hub.org>
2001-04-04 23:06 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox