Обсуждение: index on numbers not honoured
hi, an index on a table column of any number type only gets honoured if you query it like a string, e.g. create table t1 ( n int2 ) ; create index t1n on t1 (n) ; explain select * from t1 where n = 1 ; -- Seq Scan on t1 (cost=0.00..22.50 rows=10 width=2) explain select * from t1 where n = '1' ; -- Index Scan using t1n on t1 (cost=0.00..8.14 rows=10 width=2) first i thought this might be an psql client error and tried the same via jdbc, and look, there it happens again. if i create a PreparedStatemnt and bind the INT or LONG value with setLong (1,x) the index won't be used in the select statement. if i bind the value with a setString (1,x+"") command, then the index is honored correctly. I tested the code against postgres 7.1.3 as well as 7.0.2. this means that i would have to change all my java code from setLong to setString in order to speed up my apps every time i query a number. quite ugly! ilker -) -- -- gate5 AG schoenhauser allee 62 10437 berlin fon + 49 30 446 76 0 fax + 49 30 446 76 555 http://www.gate5.de/ | ilker@gate5.de
On Mon, 12 Nov 2001, Ilker Egilmez wrote: > an index on a table column of any number type only gets honoured if you > query it like a string, e.g. > > create table t1 ( n int2 ) ; > > create index t1n on t1 (n) ; > > explain select * from t1 where n = 1 ; > > -- Seq Scan on t1 (cost=0.00..22.50 rows=10 width=2) > > explain select * from t1 where n = '1' ; > > -- Index Scan using t1n on t1 (cost=0.00..8.14 rows=10 width=2) > > first i thought this might be an psql client error and tried the same via > jdbc, and look, there it happens again. if i create a PreparedStatemnt and > bind the INT or LONG value with setLong (1,x) the index won't be used in the > select statement. if i bind the value with a setString (1,x+"") command, > then the index is honored correctly. I tested the code against postgres > 7.1.3 as well as 7.0.2. this means that i would have to change all my java > code from setLong to setString in order to speed up my apps every time i > query a number. quite ugly! The problem is that the constant is being assumed to be int4 in the former statement and it won't use the index on the int2=int4 case. IIRC, the second postpones determining the type. The same thing happens on int8 columns as well I think. For int2, you're possibly best off just moving to int4 :(. There's been talk about trying to do a similar delaying thing for numeric constants but I think there were difficulties involved (I think the -general or -hackers archives will have more information)
Hi Ilker! I've tried this: create table test(a int4); copy test from stdin; 2364786 324587 2348409 1298 34980 423498 23 453587 3948 \. create index i on test(a); and found that "select ... ='5'" and "select ... =5" use seq scan too. I think it is because the planner/optimizer thinks that this question is too simple for using the index (maybe I am wrong). However, the simplest way to force pg to use index (if you 're sure that it is faster) to set the variable enable_seqscan off. (imho it's nicer than setString..) I use 7.1.1 on a Debian potato. On Mon, 12 Nov 2001, Ilker Egilmez wrote: > hi, > an index on a table column of any number type only gets honoured if you > query it like a string, e.g. > create table t1 ( n int2 ) ; > create index t1n on t1 (n) ; > explain select * from t1 where n = 1 ; > -- Seq Scan on t1 (cost=0.00..22.50 rows=10 width=2) > explain select * from t1 where n = '1' ; > -- Index Scan using t1n on t1 (cost=0.00..8.14 rows=10 width=2) > first i thought this might be an psql client error and tried the same via > jdbc, and look, there it happens again. if i create a PreparedStatemnt and > bind the INT or LONG value with setLong (1,x) the index won't be used in the > select statement. if i bind the value with a setString (1,x+"") command,
On Thu, 15 Nov 2001, Ilker Egilmez wrote: > hi risko, > try to create your table, then create your index and then insert your values. > this time the index will be used - very strange! > ilker -) I summed it: show enable_seqscan; show enable_indexscan; drop index i; drop table test; create table test(a int4); create index i on test(a); copy test from stdin; 65 87 23 \. explain select * from test where a=87; drop index i; explain select * from test where a=87; create index i on test (a); explain select * from test where a=87; I experienced it too, first indexed, but then seqscan and seqscan again. I think it is because the query optimizer. I advise you to control manually in these cases the behavior of it by setting the enable_seqscan variable (I often do it :).
On Fri, 16 Nov 2001, Risko Peter wrote: > On Thu, 15 Nov 2001, Ilker Egilmez wrote: > > hi risko, > > try to create your table, then create your index and then insert your values. > > this time the index will be used - very strange! > > ilker -) > I summed it: > > show enable_seqscan; > show enable_indexscan; > drop index i; > drop table test; > create table test(a int4); > create index i on test(a); > copy test from stdin; > 65 > 87 > 23 > \. > explain select * from test where a=87; > drop index i; > explain select * from test where a=87; > create index i on test (a); > explain select * from test where a=87; > > I experienced it too, first indexed, but then seqscan and seqscan again. > I think it is because the query optimizer. I advise you to control > manually in these cases the behavior of it by setting the enable_seqscan > variable (I often do it :). Well, you're going to need a vacuum analyze in there between the copy and first select to get something meaningful. The reason it probably uses an index on the first one is that the default statistics will choose that. However, that is almost certainly the *wrong* plan for all three of those queries. Choosing from a table that's one page should almost certainly use sequence scan and choosing one row of three should in most cases as well (see past discussions for details)