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?
Re: Why my queryes doesnt not use indexes? |
Список | 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 по дате отправления: