From: Richard Huxton <dev@archonet.com>
To: Mezei Zoltán <mezei.zoltan@telefor.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Deceiding which index to use
Date: Fri, 09 Mar 2007 14:38:22 +0000
Message-ID: <45F1715E.1050000@archonet.com> (raw)
In-Reply-To: <45F15F0F.8080403@telefor.hu>
References: <45F15F0F.8080403@telefor.hu>
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
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: dev@archonet.com, mezei.zoltan@telefor.hu
Subject: Re: Deceiding which index to use
In-Reply-To: <45F1715E.1050000@archonet.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