Thread: Basic question about indexes/explain

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
SELECTsare 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
perhapsit is because too few rows in the table ?).... 

Thanks..

Am Samstag, 26. Oktober 2002 12:27 schrieb Terry Yapt:
> Hi all,
>
snip
> I have a basic doubt about indexes...  in the next example:
> 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 ?)....

First of all, you did not analyze your table (at least you did not mention you did). And an index is never a win for
sucha small table. I think the planner is fine here to select a seq scan, because your whole table is only 1 database
page,so it would be no win to check the index here. 

Everything is explained in the manual, check http://developer.postgresql.org/docs/postgres/indexes.html

regards,
    mario weilguni

Mario Weilguni <mweilguni@sime.com> writes:
> Everything is explained in the manual, check
> http://developer.postgresql.org/docs/postgres/indexes.html

In particular note the comments at the bottom of
http://developer.postgresql.org/docs/postgres/performance-tips.html:

"It is worth noting that EXPLAIN results should not be extrapolated to
situations other than the one you are actually testing; for example,
results on a toy-sized table can't be assumed to apply to large
tables. The planner's cost estimates are not linear and so it may well
choose a different plan for a larger or smaller table. An extreme
example is that on a table that only occupies one disk page, you'll
nearly always get a sequential scan plan whether indexes are available
or not. The planner realizes that it's going to take one disk page read
to process the table in any case, so there's no value in expending
additional page reads to look at an index."

            regards, tom lane