From: Bryan White <bryan@arcamax.com>
To: PgSQL-sql <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] RE: Optimizing performance using indexes
Date: Fri, 16 Oct 1998 12:49:50 -0400
Message-ID: <007301bdf924$fe0018c0$a3f0f6ce@bryan.arcamax.com> (raw)
>On Thu, 15 Oct 1998, Jackson, DeJuan wrote:
>
>> > = create table aaa (num int2, name char(16))
>> > = create index ax on aaa (num);
>> > = create table bbb (num int2, name char(16));
>> > = create index bx on bbb (num);
>> >
>> > = explain select * from aaa where num in (select num from bbb where
>> > name = '123');
>> > Seq Scan on aaa (cost=42.00 size=101 width=14)
>> > SubPlan
>> > -> Seq Scan on bbb (cost=0.00 size=0 width=2)
>> >
>> > subquery in the example above must to use indexes. Why it doesn't so?
>> Because you aren't selecting on the indexed field... :)
>
>It seems I don't understand some principles of indexes :-( After creating
>
>= create index bx1 on bbb (num, name);
>
>PostgreSQL still uses Seq scan for subquery, but index only for "name"
>field works fine. Why so? Why index on two fields doesn't works when there
>is one field in WHERE?
An index on (num,name) can be used when looking up by num or num and name.
It has no value when looking up by name alone.
Given this data:
2,a
1,d
3,b
1,a
The index will be in this order:
1,a
1,d
2,a
3,b
The occurances of 'a' in the index order are not contiguous so a linear
search will still have to be done.
In fact AFAIK if your num field is unque number, then there is nothing to be
gained by having additional fields listed after it in an index. It just
makes the index larger than it needs to be.
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: bryan@arcamax.com
Subject: Re: [SQL] RE: Optimizing performance using indexes
In-Reply-To: <007301bdf924$fe0018c0$a3f0f6ce@bryan.arcamax.com>
References: <007301bdf924$fe0018c0$a3f0f6ce@bryan.arcamax.com>
* 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