pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Optimizing perfomance using indexes
3+ messages / 3 participants
[nested] [flat]

* Optimizing perfomance using indexes
@ 1998-10-15 12:35  Vladimir Litovka <pgsqll@barnet.kharkov.ua>
  0 siblings, 1 reply; 3+ messages in thread

From: Vladimir Litovka @ 1998-10-15 12:35 UTC (permalink / raw)
  To: pgsql-sql

Hello!

 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 ?
 As I understand, there is no big difference between queries. Are there
 general recommendations on creating indexes?

 This questions because I'm relatively new to SQL and hope somebody can
 help me :) Thank you.

---
Vladimir Litovka <doka@webest.com>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Optimizing perfomance using indexes
@ 1998-10-15 15:09  Oleg Bartunov <oleg@sai.msu.su>
  parent: Vladimir Litovka <pgsqll@barnet.kharkov.ua>
  0 siblings, 1 reply; 3+ messages in thread

From: Oleg Bartunov @ 1998-10-15 15:09 UTC (permalink / raw)
  To: doka@root.webest.com; +Cc: pgsql-sql; pgsql-hackers@postgreSQL.org

Hello,

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 !

	Regards,
        	Oleg

On Thu, 15 Oct 1998, Vladimir Litovka wrote:

> Date: Thu, 15 Oct 1998 15:35:53 +0300 (EEST)
> From: Vladimir Litovka <pgsqll@barnet.kharkov.ua>
> Reply-To: doka@root.webest.com
> To: PgSQL-sql <pgsql-sql@postgreSQL.org>
> Subject: [SQL] Optimizing perfomance using indexes
> 
> Hello!
> 
>  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 ?
>  As I understand, there is no big difference between queries. Are there
>  general recommendations on creating indexes?
> 
>  This questions because I'm relatively new to SQL and hope somebody can
>  help me :) Thank you.
> 
> ---
> Vladimir Litovka <doka@webest.com>
> 
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [HACKERS] Re: [SQL] Optimizing perfomance using indexes
@ 1998-10-16 06:10  Thomas G. Lockhart <lockhart@alumni.caltech.edu>
  parent: Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 0 replies; 3+ messages in thread

From: Thomas G. Lockhart @ 1998-10-16 06:10 UTC (permalink / raw)
  To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-sql; pgsql-hackers@postgreSQL.org

> 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1998-10-16 06:10 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-15 12:35 Optimizing perfomance using indexes Vladimir Litovka <pgsqll@barnet.kharkov.ua>
1998-10-15 15:09 ` Oleg Bartunov <oleg@sai.msu.su>
1998-10-16 06:10   ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox