Skip site navigation (1) Skip section navigation (2)

Re: Problem with index using regular expression

From: Federico Di Gregorio <federico(dot)digregorio(at)dndg(dot)it>
To: psycopg(at)postgresql(dot)org
Subject: Re: Problem with index using regular expression
Date: 2011-06-06 06:58:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: psycopg
On 06/06/11 08:32, Håvard Wahl Kongsgård wrote:
> Hi, with psycopg 2-2.4.1 I have some issue with incorrect use of indexes
> using regular expression.
> I my case I have create on index on the surname of users. 
> CREATE INDEX  income_surname ON income(substring(
> <> from E'\\w.*\\s(\\w.*)$|\\w+\\s(\\w.*)$'))
> then used
> SELECT Distinct On ( <>) nodes.ID from nodes
> inner join income on substring( <> from
> E'\\w.*\\s(\\w.*)$|\\w+\\s(\\w.*)$') = substring(
> <> from E'\\w.*\\s(\\w.*)$|\\w+\\s(\\w.*)$')
> with psql the index works fine, but with psycopg I don't think it's used
> at all. The index substring has to be identical with the substring in
> the query. Are there some encoding issues with psycopg?

psycopg does encode your strings to the backend encoding but this
shouldn't be a problem. You can:

1) Send us the code so we can analyze it.
2) Enable PostgreSQL query logging and check the query sent by psycopg.
3) I feel you're doing something wrong with the '\' in the regular
   expression. Can you should us the Python of the execute() call?


Federico Di Gregorio                         federico(dot)digregorio(at)dndg(dot)it
Studio Associato Di Nunzio e Di Gregorio        
                           There's no certainty - only opportunity. -- V

In response to


psycopg by date

Next:From: Håvard Wahl KongsgårdDate: 2011-06-06 07:03:58
Subject: Re: Problem with index using regular expression
Previous:From: Håvard Wahl KongsgårdDate: 2011-06-06 06:32:41
Subject: Problem with index using regular expression

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group