From: "Dave Dutcher" <dave@tridecap.com>
To: "'Alexandru Coseru'" <alexandru.coseru@totaltelecom.ro>,
	<pgsql-performance@postgresql.org>
Subject: Re: Regex performance issue
Date: Sat, 2 Dec 2006 14:36:49 -0600
Message-ID: <030b01c71651$984577c0$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-reply-to: <00d901c71644$326b0490$3cb16956@alex>
Importance: Normal

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org On Behalf Of Alexandru =
Coseru
> asterisk=3D> explain analyze SELECT * FROM destlist WHERE=20
> '0039051248787' ~=20
> prefix AND id_ent=3D'-2' AND dir=3D0 ORDER by length(prefix) DESC;
>
>                                                             =20
> QUERY PLAN
> --------------------------------------------------------------
> ----------------------------------------------------------------------
>  Sort  (cost=3D7925.07..7925.15 rows=3D31 width=3D67) (actual=20
> time=3D857.715..857.716 rows=3D2 loops=3D1)
>    Sort Key: length((prefix)::text)
>    ->  Bitmap Heap Scan on destlist  (cost=3D60.16..7924.30=20
> rows=3D31 width=3D67)=20
> (actual time=3D2.156..857.686 rows=3D2 loops=3D1)
>          Recheck Cond: ((id_ent =3D -2) AND (dir =3D 0))
>          Filter: ('0039051248787'::text ~ (prefix)::text)
>          ->  Bitmap Index Scan on destlist_indx2  (cost=3D0.00..60.16=20
> rows=3D6193 width=3D0) (actual time=3D1.961..1.961 rows=3D5205 =
loops=3D1)
>                Index Cond: ((id_ent =3D -2) AND (dir =3D 0))
>  Total runtime: 857.804 ms
> (8 rows)
>=20
>
>     "mmumu" btree (prefix varchar_pattern_ops)
>=20

I'm surpised Postgres isn't using the index on prefix seeing as the =
index
uses the varchar_pattern_ops operator class.  It could be that the index
isn't selective enough, or is Postgres not able to use an index with =
Posix
regular expressions?  The docs seem to say that it can, but I'd be =
curious
to see what happens if you use LIKE instead of ~.=20

Dave
=20