Skip site navigation (1) Skip section navigation (2)

Re: Query on postgresql 7.4.2 not using index

From: "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
To: arnaulist(at)andromeiberica(dot)com
Cc: "chris smith" <dmagick(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Query on postgresql 7.4.2 not using index
Date: 2006-04-25 14:31:51
Message-ID: 1d4e0c10604250731k5aac183eg92919020bfb3ff27@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 4/25/06, Arnau <arnaulist(at)andromeiberica(dot)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

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2006-04-25 15:05:02
Subject: Re: Query on postgresql 7.4.2 not using index
Previous:From: ArnauDate: 2006-04-25 13:49:33
Subject: Re: Query on postgresql 7.4.2 not using index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group