From: | "Thomas G(dot) Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu> |
---|---|
To: | Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> |
Cc: | doka(at)root(dot)webest(dot)com, PgSQL-sql <pgsql-sql(at)postgreSQL(dot)org>, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] Re: [SQL] Optimizing perfomance using indexes |
Date: | 1998-10-16 06:10:54 |
Message-ID: | 3626E36E.8794EE14@alumni.caltech.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-10-16 06:15:06 | Re: [HACKERS] What about LIMIT in SELECT ? |
Previous Message | Bruce Momjian | 1998-10-16 06:05:28 | Re: [HACKERS] Odd problem with read_pg_options ... |
From | Date | Subject | |
---|---|---|---|
Next Message | Vladimir Litovka | 1998-10-16 11:51:49 | RE: Optimizing performance using indexes |
Previous Message | Walt Bigelow | 1998-10-16 01:25:35 | Optimizing again |