Received: from golem.jpl.nasa.gov (root@hectic-4.jpl.nasa.gov [128.149.68.206]) by hub.org (8.8.8/8.8.8) with ESMTP id CAA12768; Fri, 16 Oct 1998 02:10:33 -0400 (EDT) (envelope-from lockhart@alumni.caltech.edu) Received: from alumni.caltech.edu (localhost [127.0.0.1]) by golem.jpl.nasa.gov (8.8.5/8.8.5) with ESMTP id GAA26122; Fri, 16 Oct 1998 06:10:55 GMT Sender: tgl@mythos.jpl.nasa.gov Message-ID: <3626E36E.8794EE14@alumni.caltech.edu> Date: Fri, 16 Oct 1998 06:10:54 +0000 From: "Thomas G. Lockhart" Organization: Caltech/JPL X-Mailer: Mozilla 4.07 [en] (X11; I; Linux 2.0.30 i686) MIME-Version: 1.0 To: Oleg Bartunov CC: doka@root.webest.com, PgSQL-sql , pgsql-hackers@postgreSQL.org Subject: Re: [HACKERS] Re: [SQL] Optimizing perfomance using indexes References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit > What version of PostgreSQL you're talking about ? > I also noticed such behaivour in 6.4 beta, try > int4 instead of int2 and see what happens. I don't know the reason > but in my case it works. 6.3.2 uses indices in both cases ! > > There are examples below and can anybody explain me - how to use > > indexes in PostgreSQL for best perfomance? Look here: > > create table aaa (num int2, name text); > > create index ax on aaa (num); > > explain select * from aaa where num = 5; > > Index Scan on aaa (cost=0.00 size=0 width=14) > > explain select * from aaa where num > 5; > > Seq Scan on aaa (cost=0.00 size=0 width=14) > > Why PostgreSQL in the first case uses index, but in the second - > > doesn't ? For Postgres (all versions), the "5" is read as an int4 in the scanner (before parsing). Your column is int2. In v6.3.2 and before, the _only_ mechanism for implicit type conversion/coersion was to convert constants to strings and then convert the strings back to constants. No other situation was handled, so implicit conversion between any non-constant was not allowed. Vladimir is probably running v6.3.2 or before? For v6.4, the Postgres parser looks for _functions_ to convert types, for constants and for every other situation. Also, there needs to be a "promotion" of types so that, for example, int4's are not forced to become int2's, with the risk of overflow (another drawback with the old scheme: "where num < 100000" would fail or overflow since the 100000 was forced to be an int2). So with v6.4 your query select * from aaa where num = 5; becomes select * from aaa where int4(num) = 5; which has a hard time using an int2 index. I plan on increasing support for function calls and indices in v6.5. In the meantime, you can specify your query as select * from aaa where num = '5'; which will choose the type for the string constant from the other argument "num". Or you can be explicit: select * from aaa where num = int2 '5'; -- SQL92 select * from aaa where num = '5'::int2; -- old Postgres There is a chapter in the User's Guide ("Type Conversion") in the v6.4 docs which discusses this; if you want to look at the beta docs let me know if it needs more info... - Tom