From: Guillaume Smet <guillaume.smet@gmail.com>
To: arnaulist@andromeiberica.com
Cc: chris smith <dmagick@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query on postgresql 7.4.2 not using index
Date: Tue, 25 Apr 2006 16:31:51 +0200
Message-ID: <1d4e0c10604250731k5aac183eg92919020bfb3ff27@mail.gmail.com> (raw)
In-Reply-To: <444E28ED.3090900@andromeiberica.com>
References: <444E240D.3050000@andromeiberica.com>
<3c1395330604250634r6f215ceame157f3dd5f5f4fa3@mail.gmail.com>
<444E28ED.3090900@andromeiberica.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: guillaume.smet@gmail.com, arnaulist@andromeiberica.com, dmagick@gmail.com
Subject: Re: Query on postgresql 7.4.2 not using index
In-Reply-To: <1d4e0c10604250731k5aac183eg92919020bfb3ff27@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox