Re: FAQ -- Index usage/speed
От | Bruce Momjian |
---|---|
Тема | Re: FAQ -- Index usage/speed |
Дата | |
Msg-id | 200408311905.i7VJ5sv16159@candle.pha.pa.us обсуждение исходный текст |
Ответ на | FAQ -- Index usage/speed (Thomas F.O'Connell <tfo@sitening.com>) |
Ответы |
Re: FAQ -- Index usage/speed
(Thomas F.O'Connell <tfo@sitening.com>)
Re: FAQ -- Index usage/speed (Josh Berkus <josh@agliodbs.com>) |
Список | pgsql-docs |
Yes, 4.8 would be the right spot. Not sure why we got so many reports recently though. However, with this fixed in 8.0, it probably isn't worth adding to the FAQ. --------------------------------------------------------------------------- Thomas F. O'Connell wrote: > Bruce, > > Considering the activity on the lists (at least recently and, I think, > historically) about postgres not casting (usually integer) constant > values across types, could there be a mention of this made in the FAQ? > It seems like a logical case for inclusion under 4.8: > > http://www.postgresql.org/docs/faqs/FAQ.html#4.8 > > I was thinking something like the following: > > Also note that 7.x versions of postgres will not automatically cast > constant data in certain queries such that an index would be used. For > example, if you have the following: > > CREATE TABLE index_breaker ( > bigintcol int8 primary key > some_data text > ); > > The following query is liikely to perform a sequential scan: > > SELECT some_data FROM index_breaker WHERE bigintcol = 42; > > postgres will interpret the constant value as a basic int and will thus > not use the index (implicitly created by the primary key) on the > bigintcol column. > > There are some workarounds for this issue [per Tom Lane]: > > 1. Always quote your constants: > > ... WHERE bigintcol = '42'; > > Similarly, constants can be explicitly cast: > > ... WHERE bigintcol = int8( 42 ) > > 2. Use a prepared statement: > > PREPARE foo(bigint) AS ... WHERE bigintcol = $1; > EXECUTE foo(42); > > 3. Use parameterized statements in extended-query mode (essentially the > same idea as #2, but at the protocol level). This doesn't help for > pure SQL scripts, but is very workable when coding against libpq or > JDBC. Among other things it gets you out of worrying about SQL > injection attacks when your parameter values come from untrusted > sources. > > > Technical improvements to wording are welcome. But I think this is > worth adding to the docs somewhere. > > Thanks! > > -tfo > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
В списке pgsql-docs по дате отправления: