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