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
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