[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] 8.2 planner and "like"
From: "Angva" <angvaw () gmail ! com>
Date: 2007-01-30 22:30:55
Message-ID: 1170196255.681616.58860 () q2g2000cwa ! googlegroups ! com
[Download RAW message or body]
Thanks so much for that info, Tom!
On Jan 29, 8:00 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> "Angva" <ang...@gmail.com> writes:
> > Our company's application runs searches with "like" where clauses, for
> > example: "where id like '38F20A%'". This query once ran in under 10 ms,
> > but since upgrading from 8.1.3 to 8.2.0, it now takes about 500 ms to
> > run. The problem appears to be that the planner does not want to use an
> > index since the upgrade, for "like" queries.
> > We experimented with planner cost constants, and we even tried setting
> > enable_seqscan to false. A seq scan was done every time.
>
> If it won't do it even with seqscan off, then it can't, which most
> likely means that you mistakenly picked a non-C locale for the new
> installation. You can either fix that (requiring re-initdb :-() or
> provide a "pattern_ops" index that's not subject to locale oddities.
> Non-C locales tend to impose a sort ordering that's not suited for
> LIKE pattern matching. Seehttp://www.postgresql.org/docs/8.2/static/indexes-opclass.html
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic