Received: from sss.pgh.pa.us ([216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f34N6A814541 for ; Wed, 4 Apr 2001 19:06:10 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f34N64w13002; Wed, 4 Apr 2001 19:06:04 -0400 (EDT) To: The Hermit Hacker cc: pgsql-sql@postgresql.org Subject: Re: [7.0.3] optimizing a LIKE query ... In-reply-to: References: Comments: In-reply-to The Hermit Hacker message dated "Wed, 04 Apr 2001 11:59:47 -0300" Date: Wed, 04 Apr 2001 19:06:03 -0400 Message-ID: <12999.986425563@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200104/67 X-Sequence-Number: 2406 The Hermit Hacker 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