Re: Deceiding which index to use - Mailing list pgsql-performance

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