Re: Query on postgresql 7.4.2 not using index

On 4/25/06, Arnau <arnaulist@andromeiberica.com> wrote:
> espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
> agenda_users_groups
> espsm_moviltelevision-# WHERE group_id = '9';
>                                                             QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------
>   Seq Scan on agenda_users_groups  (cost=0.00..53108.45 rows=339675
> width=8) (actual time=916.903..5763.830 rows=367026 loops=1)
>     Filter: (group_id = 9::numeric)
>   Total runtime: 7259.861 ms
> (3 filas)

Arnau,

Why do you use a numeric instead of an integer/bigint??

IIRC, there were a few problems with index on numeric column on older
version of PostgreSQL.

You can't change the type of a column with 7.4, so create a new
integer column then copy the values in this new column, drop the old
one, rename the new one. Run vacuum analyze and recreate your index.

It should work far better with an int.

Note that you will have to update all the tables referencing this key...

--
Guillaume