FAQ
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 ;)

Search Discussions

  • Sam Mason at Jun 22, 2009 at 11:22 pm

    On Mon, Jun 22, 2009 at 08:43:43AM -0700, DaNieL wrote:
    Hi guys, i am in trouble with some simple data that seem like doesnt
    use any index.. and i dont know why.
    It can be for a couple of reasons; firstly using an index isn't always a
    good thing. In your case I'd guess you probably want to probably want
    to do the following when creating the index:
    CREATE INDEX users_nick_index ON users (nickname varchar_pattern_ops);
    That will allow PG to use the index in LIKE expressions. For more
    details see:

    http://www.postgresql.org/docs/current/static/indexes-opclass.html
  • Grzegorz Jaśkiewicz at Jun 23, 2009 at 7:55 am
    common mistake is - you didn't vacuum analyze your table at all, after
    inserting so much data in it.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 22, '09 at 4:10p
activeJun 23, '09 at 7:55a
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase