From: Dimitri Fontaine <dfontaine@hi-media.com>
Organization: Hi-Media
To: pgsql-performance@postgresql.org
Subject: Re: Planner should use index on a LIKE 'foo%' query
Date: Mon, 30 Jun 2008 12:20:40 +0200
User-Agent: KMail/1.9.9
Cc: Moritz Onken <onken@houseofdesign.de>
References: <287E4428-BECC-46D6-BC47-EA94EA0121F0@houseofdesign.de>
In-Reply-To: <287E4428-BECC-46D6-BC47-EA94EA0121F0@houseofdesign.de>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart1472821.x6qPyVXzE2";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200806301220.43392.dfontaine@hi-media.com>

--nextPart1472821.x6qPyVXzE2
Content-Type: text/plain;
  charset="windows-1252"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

Le samedi 28 juin 2008, Moritz Onken a =E9crit=A0:
> select count(*)
>   from result
>   where exists
>     (select * from item where item.url LIKE result.url || '%' limit 1);
>
> which basically returns the number of items which exist in table
> result and match a URL in table item by its prefix.

It seems you could benefit from the prefix project, which support indexing=
=20
your case of prefix searches. Your query would then be:
  SELECT count(*) FROM result r JOIN item i ON r.url @> i.url;

The result.url column would have to made of type prefix_range, which casts=
=20
automatically to text when needed.

=46ind out more about the prefix projects at those urls:
  http://pgfoundry.org/projects/prefix
  http://prefix.projects.postgresql.org/README.html

Regards,
=2D-=20
dim

--nextPart1472821.x6qPyVXzE2
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkhos3kACgkQlBXRlnbh1bn6UACfbCKy5la/0Vl5fTPxDvJcTi9B
E18AoKkz/60/2xDYbt1ThW7m+Bw16o9f
=aP7c
-----END PGP SIGNATURE-----

--nextPart1472821.x6qPyVXzE2--