Basic question about indexes/explain

Поиск
Список
Период
Сортировка
От Terry Yapt
Тема Basic question about indexes/explain
Дата
Msg-id 1035628048.81bd0c0pgsql@technovell.com
обсуждение исходный текст
Ответы Re: Basic question about indexes/explain
Список 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
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..


В списке pgsql-performance по дате отправления:

Предыдущее
От: Tomasz Myrta
Дата:
Сообщение: Re: joining views
Следующее
От: Mario Weilguni
Дата:
Сообщение: Re: Basic question about indexes/explain