RE: [SQL] Optimizing performance using indexes - Mailing list pgsql-sql

From Jackson, DeJuan
Subject RE: [SQL] Optimizing performance using indexes
Date
Msg-id F10BB1FAF801D111829B0060971D839F4792AA@cpsmail
Whole thread Raw
List 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>
>
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


pgsql-sql by date:

Previous
From: pierre@desertmoon.com
Date:
Subject: Cluster(ing) to help joins?
Next
From: Oleg Bartunov
Date:
Subject: Re: [SQL] Optimizing perfomance using indexes