Why my queryes doesnt not use indexes?

Поиск
Список
Период
Сортировка
От DaNieL
Тема Why my queryes doesnt not use indexes?
Дата
Msg-id c604ce78-b873-4d44-b520-c7b5aef36b43@j18g2000yql.googlegroups.com
обсуждение исходный текст
Ответы Re: Why my queryes doesnt not use indexes?  (Sam Mason <sam@samason.me.uk>)
Re: Why my queryes doesnt not use indexes?  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
Список pgsql-general
Hi guys, i am in trouble with some simple data that seem like doesnt
use any index.. and i dont know why.
My test database structure is this one:

------
CREATE TABLE users(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    nickname varchar(50),
    email varchar(50) NOT NULL
);

CREATE INDEX users_nick_index ON users (nickname);
CREATE UNIQUE INDEX users_email_uindex ON users (email);

INSERT INTO users (nickname, email) VALUES ('foo', 'foo@example.com');
INSERT INTO users (nickname, email) VALUES ('bar', 'bar@example.com');
-----

Now, i populated the database with around 5000 rows.

If i run that query:

------------
EXPLAIN SELECT email FROM users WHERE nickname = 'Errol'
------------

The result is:
------------
QUERY PLAN
Bitmap Heap Scan on users  (cost=4.37..36.04 rows=15 width=28)
  Recheck Cond: ((nickname)::text = 'Errol'::text)
  ->  Bitmap Index Scan on users_nick_index  (cost=0.00..4.36 rows=15
width=0)
        Index Cond: ((nickname)::text = 'Errol'::text)
-----------

So seem that it use the index.. but if i use the LIKE:

---------
EXPLAIN SELECT email FROM users WHERE nickname LIKE 'E'
----------
Postgresql dont use any index, and run with a seq scan:

---------
QUERY PLAN
Seq Scan on users  (cost=0.00..112.05 rows=15 width=28)
  Filter: ((nickname)::text ~~ 'E'::text)
----------

anyone can explain me why?

Im just structuring a db for a new application, if there is any
problem i'll like to solve it now ;)

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

Предыдущее
От: BlackMage
Дата:
Сообщение: Inserting Values into Interval
Следующее
От: Arndt Lehmann
Дата:
Сообщение: Re: Trigger Function and backup