Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.8.8/8.8.8) with ESMTP id LAA16377 for ; Thu, 15 Oct 1998 11:23:28 -0400 (EDT) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id KAA23540; Thu, 15 Oct 1998 10:23:25 -0500 (CDT) Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (4.1) id xmaa22764; Thu, 15 Oct 98 10:22:41 -0500 Received: by cpsmail with Internet Mail Service (5.0.1458.49) id <4KHRJZB8>; Thu, 15 Oct 1998 10:25:12 -0500 Message-ID: From: "Jackson, DeJuan" To: doka@root.webest.com, PgSQL-sql Subject: RE: [SQL] Optimizing performance using indexes Date: Thu, 15 Oct 1998 10:25:11 -0500 X-Priority: 3 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.0.1458.49) Content-Type: text/plain > 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 > 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