Re: Problem with index using regular expression

conn = psycopg2.connect(database="tax_analytical", user="postgres",
password="xxxx", host='xxxx')

cur = conn.cursor('nodes_database')

SQL = ("SELECT Distinct On (nodes.id) nodes.ID from nodes inner join income
on substring(nodes.name from E'\\w.*\\s(\\w.*)$|\\w+\\s(\\w.*)$') =
substring(income.name from E'\\w.*\\s(\\w.*)$|\\w+\\s(\\w.*)$') limit 1000")

cur.execute(SQL)

if __name__ == '__main__':
pool = multiprocessing.Pool(processes=5)
pool.map(network_file.main, cur, 1) # Ensure the chunk size is 1
pool.close()
pool.join()

cur.close()
conn.close()

-Håvard Wahl Kongsgård

On Mon, Jun 6, 2011 at 8:58 AM, Federico Di Gregorio <
federico(dot)digregorio(at)dndg(dot)it> wrote:

> 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(income.name
> > <http://income.name> from E'\\w.*\\s(\\w.*)$|\\w+\\s(\\w.*)$'))
> >
> > then used
> > SELECT Distinct On (nodes.id <http://nodes.id>) nodes.ID from nodes
> > inner join income on substring(nodes.name <http://nodes.name> from
> > E'\\w.*\\s(\\w.*)$|\\w+\\s(\\w.*)$') = substring(income.name
> > <http://income.name> 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
>
> --
> Federico Di Gregorio federico(dot)digregorio(at)dndg(dot)it
> Studio Associato Di Nunzio e Di Gregorio http://dndg.it
> There's no certainty - only opportunity. -- V
>
> --
> Sent via psycopg mailing list (psycopg(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
>