Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group