Re: Joins on many-to-many relations. - Mailing list pgsql-sql

From Rodrigo De León
Subject Re: Joins on many-to-many relations.
Date
Msg-id a55915760703150705j3b4c2b3esd94d4a27d31ee658@mail.gmail.com
Whole thread Raw
In response to Re: Joins on many-to-many relations.  (Wiebe Cazemier <halfgaar@gmx.net>)
List pgsql-sql
On 3/14/07, Wiebe Cazemier <halfgaar@gmx.net> wrote:
> I discovered that removing the subselect (the entire second condition of
> the join actually) is not the only thing that speeds it up. If I remove
> the LIKE check on account.description, it's also a lot faster (152 ms
> as opposed to 2915 ms), although not as fast as without the subselect.
> I don't understand why that makes such a big difference. There is an
> index on upper() on the field.

From http://www.postgresql.org/docs/8.2/static/indexes-types.html :

"The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE and ~ if the pattern is a constant and
is anchored to the beginning of the string — for example, col LIKE
'foo%' or col ~ '^foo', but not col LIKE '%bar'."


pgsql-sql by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: Alter Table
Next
From: Wiebe Cazemier
Date:
Subject: Re: Joins on many-to-many relations.