Basic question about indexes/explain

From: Terry Yapt <pgsql(at)technovell(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Basic question about indexes/explain
Date: 2002-10-26 10:27:28
Message-ID: 1035628048.81bd0c0pgsql@technovell.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all,

I have a basic doubt about indexes... in the next example:

-- ====================================================
DROP TABLE ctest;
CREATE TABLE ctest
( cusid numeric(5) PRIMARY KEY NOT NULL, -- Customer ID.
namec varchar(10) NOT NULL, -- Customer Name.
surnc varchar(20), -- Customer Surname.
cashc numeric(10,4) -- Customer Cash.
);
CREATE INDEX ctest_cashc ON ctest (cashc);

INSERT INTO ctest VALUES (10,'Ten Custom','S.Ten Customer',1000);
INSERT INTO ctest VALUES (5 ,'Five Custo','S.Five Customer',500);
INSERT INTO ctest VALUES (8, 'Eigth Cust','S.Eigth Customer',800);
INSERT INTO ctest VALUES (90,'Nine Custo','S.Nine Customer',9000);
INSERT INTO ctest VALUES (70,'Seven Cust','S.Seven Customer',7000);

-- Next two SELECT will execute using index Scan on ctest_pkey
explain SELECT * from ctest WHERE cusid between 5 AND 10 AND cashc < 1000;
explain SELECT * from ctest WHERE cusid =5 AND cashc = 1000;

CREATE INDEX ctest_othec ON ctest (cusid, cashc);

-- Next two SELECT will execute using Seq Scan.
explain SELECT * from ctest WHERE cusid between 5 AND 10 AND cashc < 1000;
explain SELECT * from ctest WHERE cusid =5 AND cashc = 1000;

-- ====================================================

SELECTs executed before CREATE INDEX ctest_othec... are using index scan on PRIMARY KEY, but after the CREATE INDEX all SELECTs are using seq scan.

Seq Scan has lower cost than index scan (I think because there are few rows in table).

But if we have an index with the two colums I am using in the WHERE clause, why is the planner using seq scan ? (Or perhaps it is because too few rows in the table ?)....

Thanks..

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mario Weilguni 2002-10-26 11:40:06 Re: Basic question about indexes/explain
Previous Message Tomasz Myrta 2002-10-23 15:02:34 Re: joining views