X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 85C9711F6236 for ; Tue, 25 Apr 2006 11:32:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 24738-05 for ; Tue, 25 Apr 2006 11:31:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.204]) by postgresql.org (Postfix) with ESMTP id 77F4911F6207 for ; Tue, 25 Apr 2006 11:31:51 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id 9so1061175nzo for ; Tue, 25 Apr 2006 07:31:52 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=HCfl0Uy2+yq6qERt5t+KWR86eWp5zEIWl8EemZksGvPmqASaoxXp8OJLTtRLoFPKfvJipiU13LrNLI+sRsYjZ7SoCeykr7bRUNQ4EhvRAt+Gj6IInuFwWyomfi6TxFuVLZuYtL0CLqVMxNjoramWFGy286wMYQNJ2f7kbDq7xrE= Received: by 10.65.114.5 with SMTP id r5mr175131qbm; Tue, 25 Apr 2006 07:31:51 -0700 (PDT) Received: by 10.65.122.19 with HTTP; Tue, 25 Apr 2006 07:31:51 -0700 (PDT) Message-ID: <1d4e0c10604250731k5aac183eg92919020bfb3ff27@mail.gmail.com> Date: Tue, 25 Apr 2006 16:31:51 +0200 From: "Guillaume Smet" To: arnaulist@andromeiberica.com Subject: Re: Query on postgresql 7.4.2 not using index Cc: "chris smith" , pgsql-performance@postgresql.org In-Reply-To: <444E28ED.3090900@andromeiberica.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <444E240D.3050000@andromeiberica.com> <3c1395330604250634r6f215ceame157f3dd5f5f4fa3@mail.gmail.com> <444E28ED.3090900@andromeiberica.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/547 X-Sequence-Number: 18631 On 4/25/06, Arnau wrote: > espsm_moviltelevision=3D# EXPLAIN ANALYZE SELECT agenda_user_group_id FRO= M > agenda_users_groups > espsm_moviltelevision-# WHERE group_id =3D '9'; > QUERY PLAN > -------------------------------------------------------------------------= ------------------------------------------------------- > Seq Scan on agenda_users_groups (cost=3D0.00..53108.45 rows=3D339675 > width=3D8) (actual time=3D916.903..5763.830 rows=3D367026 loops=3D1) > Filter: (group_id =3D 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