From: | DaNieL <daniele(dot)pignedoli(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Why my queryes doesnt not use indexes? |
Date: | 2009-06-22 15:43:43 |
Message-ID: | c604ce78-b873-4d44-b520-c7b5aef36b43@j18g2000yql.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)example(dot)com');
INSERT INTO users (nickname, email) VALUES ('bar', 'bar(at)example(dot)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 ;)
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2009-06-22 15:45:17 | Re: 8.2 instance that won't come up after shutdown |
Previous Message | Dario Teixeira | 2009-06-22 15:43:03 | Re: Information about columns |