Re: use of index

Rafal Pietrak wrote:
> Hi,
>
> I've stapped over the following magic:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Table "public.users"
> Column | Type |
> Modifiers
> ----------+--------------+----------------------------------------------------
> id | integer | not null default
> nextval('users_id_seq'::regclass)
> username | text | not null
> firma | integer |
> email | text |
> state | character(1) |
> Indexes:
> "users_pkey" PRIMARY KEY, btree (id)
> "users_username_key" UNIQUE, btree (username)
> Foreign-key constraints:
> "users_firma_fkey" FOREIGN KEY (firma) REFERENCES firmy(id)
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Table "public.ludzie"
> Column | Type |
> Modifiers
> ----------+--------------+-----------------------------------------------------
> id | integer | not null default
> nextval('ludzie_id_seq'::regclass)
> username | text | not null
> firma | integer |
> email | text |
> state | character(1) |
> Indexes:
> "ludzie_pkey" PRIMARY KEY, btree (id)
> "username_id_key" UNIQUE, btree (username)
> Foreign-key constraints:
> "$1" FOREIGN KEY (firma) REFERENCES firmy(id)
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> INSERT INTO users SELECT * from ludzie;
> INSERT 0 14
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> strop=# EXPLAIN ANALYZE SELECT * from users where username =
> current_user;
> QUERY
> PLAN
> ---------------------------------------------------------------------------------------------------------------------------
> Index Scan using users_username_key on users (cost=0.00..5.83 rows=1
> width=80) (actual time=0.061..0.061 rows=0 loops=1)
> Index Cond: (username = ("current_user"())::text)
> Total runtime: 0.193 ms
> (3 rows)
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> EXPLAIN ANALYZE SELECT * from ludzie where username = current_user;
> QUERY
> PLAN
> -------------------------------------------------------------------------------------------------
> Seq Scan on ludzie (cost=0.00..1.19 rows=1 width=80) (actual
> time=0.096..0.096 rows=0 loops=1)
> Filter: (username = ("current_user"())::text)
> Total runtime: 0.185 ms
> (3 rows)
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> As fak as I can tell, both TABLE users and TABLE ludzie are identical:
> 1. they have the same structure
> 2. they have the same contents (users was empty before INSERT)
> Still, the query plan on them differs, and quite signifficantly - one
> uses index, while the other doesn't.
>
> Can someone shred some light on why is that so?

Firstly always start a new thread instead of replying to an existing
one, it is a lot easier to follow and not so easily missed.

After your insert you need to do an 'analyze users' to get postgresql
statistics up to date.

Next - why does it need to use an index? In these examples your query is
finishing in less than 2ms so are very quick. Postgresql doesn't always
choose to use an index because it doesn't need to. In this case there
are only 14 rows in the table so it could be quicker for the db to look
at each row rather than using an index.

Do you have an example where it's taking a lot longer? Post those
results and you might get more of a response.

--
Postgresql & php tutorials
http://www.designmagick.com/