Обсуждение: 8.2 planner and "like"
Hello, I have a question regarding Postgres 8.2 and the planner. 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. Now if my understanding is correct, setting enable_seqscan to false should tell the planner never to use a seq scan unless no alternative is possible. We made sure the relevant index still exists, of course, and that the table was analyzed with good samples... Could this be a bug, or am I the problem? :) Any advice or insight is appreciated. Thank you, Mark
"Angva" <angvaw@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. See http://www.postgresql.org/docs/8.2/static/indexes-opclass.html regards, tom lane
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