pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRE: [SQL] Optimizing performance using indexes
4+ messages / 4 participants
[nested] [flat]
* RE: [SQL] Optimizing performance using indexes
@ 1998-10-15 15:25 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jackson, DeJuan @ 1998-10-15 15:25 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>
>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Optimizing performance using indexes
@ 1998-10-15 17:08 Vladimir Litovka <doka@root.webest.com>
0 siblings, 0 replies; 4+ messages in thread
From: Vladimir Litovka @ 1998-10-15 17:08 UTC (permalink / raw)
To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: Oleg Bartunov <oleg@sai.msu.su>
Hello!
On Thu, 15 Oct 1998, Jackson, DeJuan wrote:
I've forgotten to say: I'm using PostgreSQL 6.3.2 with patches from
ftp://ftp.postgresql.org/pub/patches :
. linux_elf.patch-980421.gz
. gram.c.patch-980428
. configure-980430.gz
. btree_adj-980730.gz
> This table doesn't have many rows in it does it.
> Drop about 100+ rows into the table. VACUUM the database and re-run the
> explains.
> Let me know how it turns out,
I've inserted 1000 rows into table:
= create table aaa (num int2, name char(16))
= create index ax on aaa (num);
= create index ax1 on aaa (num, name);
and tried explains:
= explain select * from aaa where num > 5;
Index Scan on aaa (cost=22.67 size=334 width=14)
= explain select * from aaa where num = 5;
Index Scan on aaa (cost=2.05 size=2 width=14)
= explain select * from aaa where num = 5 and name != '123';
Index Scan on aaa (cost=2.05 size=1 width=14)
It seems OK. The next I've tried subqueries and PostgreSQL has falled to Seq
scans:
= create table bbb (num int2, name char(16));
= create index bx on bbb (num);
= insert into bbb values (...)
^^^^^^-- less than 10 rows
= 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)
You said that "v6.3 and before doesn't use indexes on IN queries", but
subquery in the example above must to use indexes. Why it doesn't so?
Thank you :)
--
Vladimir Litovka <doka@webest.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Optimizing performance using indexes
@ 1998-10-16 11:51 Vladimir Litovka <pgsqll@barnet.kharkov.ua>
0 siblings, 0 replies; 4+ messages in thread
From: Vladimir Litovka @ 1998-10-16 11:51 UTC (permalink / raw)
To: pgsql-sql
Hi!
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?
> But this will run faster in PostgreSQL until the IN indexing gets
> fixed:
> SELECT *
> FROM aaa
> WHERE EXISTS(SELECT num
> FROM bbb
> WHERE name='123' AND bbb.num=aaa.num);
And next time: why? This query works: for every row in AAA do (subquery)
So there will be count(aaa) queries. As I understand, query above must do
2 select - one for internal and one for external selects. Where I'm wrong?
Thank you :)
--
Vladimir Litovka <doka@webest.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] RE: Optimizing performance using indexes
@ 1998-10-16 16:49 Bryan White <bryan@arcamax.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bryan White @ 1998-10-16 16:49 UTC (permalink / raw)
To: pgsql-sql
>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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1998-10-16 16:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-15 15:25 RE: [SQL] Optimizing performance using indexes Jackson, DeJuan <djackson@cpsgroup.com>
1998-10-15 17:08 RE: Optimizing performance using indexes Vladimir Litovka <doka@root.webest.com>
1998-10-16 11:51 RE: Optimizing performance using indexes Vladimir Litovka <pgsqll@barnet.kharkov.ua>
1998-10-16 16:49 Re: [SQL] RE: Optimizing performance using indexes Bryan White <bryan@arcamax.com>
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