pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jackson, DeJuan <djackson@cpsgroup.com>
To: doka@root.webest.com, PgSQL-sql <pgsql-sql@postgreSQL.org>
Subject: RE: [SQL] Optimizing performance using indexes
Date: Thu, 15 Oct 1998 10:25:11 -0500
Message-ID: <F10BB1FAF801D111829B0060971D839F4792AA@cpsmail> (raw)

> 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>
> 
Hi Vladimir,
	I see that you found at least one of the PostgreSQL mailing
list.

As to your question:  If you think about there has to be some overhead
for an index scan.  If you are select beck ever row in a table it makes
little since to scan an index for that table; exception is when you are
ordering the rows in the table by that index; even then it's usually
faster to actually sort than to scan all over the disk for rows.
PostgreSQL make the judgement call in favor of a sequential scan when
the optimizer reports that it will be the faster method.
I'm sure that someone else could give you a more precise explanation, so
I'll leave them to it.
	-DEJ




view thread (4+ messages)  latest in thread

Message-ID: <F10BB1FAF801D111829B0060971D839F4792AA@cpsmail>
Permalink:  ../F10BB1FAF801D111829B0060971D839F4792AA@cpsmail/
Also on:    postgresql.org/message-id/F10BB1FAF801D111829B0060971D839F4792AA@cpsmail

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: djackson@cpsgroup.com
  Subject: Re: [SQL] Optimizing performance using indexes
  In-Reply-To: <F10BB1FAF801D111829B0060971D839F4792AA@cpsmail>
  References: <F10BB1FAF801D111829B0060971D839F4792AA@cpsmail>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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