Message-ID: <45F1715E.1050000@archonet.com>
Date: Fri, 09 Mar 2007 14:38:22 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= <mezei.zoltan@telefor.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Deceiding which index to use
References: <45F15F0F.8080403@telefor.hu>
In-Reply-To: <45F15F0F.8080403@telefor.hu>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Mezei Zoltán wrote:
> Hi!
> 
> I have two tables with some indices on them:
> 
> CREATE TABLE subscriber
> (
>   id serial NOT NULL,
>   anumber character varying(32) NOT NULL,
>   CONSTRAINT subscriber_pk PRIMARY KEY (id)
> )
> 
> CREATE INDEX anumber_idx_numeric
>   ON subscriber
>   USING btree
>   (anumber::numeric);

> I would like to run a query like this one:
> 
> select l.id
> from output_message_log l join subscriber s on l.subscriber_id = s.id
> where s.anumber::numeric = 5555555555
> order by l.crd desc
> limit 41
> offset 20

Q1. Why are you storing a numeric in a varchar?
Q2. How many unique values does anumber have? And how many rows in 
subscriber?
Q3. What happens if you create the index on plain (anumber) and then 
test against '555555555'?

-- 
   Richard Huxton
   Archonet Ltd