Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f3RDRO396114 for ; Fri, 27 Apr 2001 09:27:24 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3R9Q5306717 for ; Fri, 27 Apr 2001 05:26:05 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f3R8xwl07281 for pgsql-sql@postgresql.org; Fri, 27 Apr 2001 04:59:58 -0400 (EDT) (envelope-from news) From: =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= X-Newsgroups: comp.databases.postgresql.sql Subject: Regular expressions and indexes Date: Fri, 27 Apr 2001 11:04:25 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 27 Message-ID: <3AE93619.BE51C143@cybertec.at> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.76 [de] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200104/422 X-Sequence-Number: 2784 Is there any possibility to make PostgreSQL use indexes when working with regular expressions? performance=# EXPLAIN SELECT * FROM perftest WHERE id=100; NOTICE: QUERY PLAN: Index Scan using idx_id_perftest on perftest (cost=0.00..4.98 rows=1 width=20) EXPLAIN performance=# EXPLAIN SELECT * FROM perftest WHERE id ~ '^100$'; NOTICE: QUERY PLAN: Seq Scan on perftest (cost=100000000.00..100218966.00 rows=100000 width=20) EXPLAIN It is clear that complex regular expressions can possibly never use an index but is it possible to use it when looking for the beginning of a string (e.g.: ^100). Hans