Date: Fri, 10 Sep 2004 08:39:33 +0300 (EEST)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
Cc: "'Josh Berkus'" <josh@agliodbs.com>, <pgsql-sql@postgresql.org>
Subject: Re: Isnumeric function?
In-Reply-To: <82E30406384FFB44AFD1012BAB230B55037D0591@shiva.au.lpint.net>
Message-ID: <Pine.LNX.4.44.0409100837300.3763-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-7
Content-Transfer-Encoding: 8BIT

O Theo Galanakis έγραψε στις Sep 10, 2004 :

> 
> 
> Josh,
> 
> I agree with the machete technique, unfortunately The structure is inplace
> and a work-around was required.
> 
> I created the Index you specified, however it chooses to run a seq scan on
> the column rather than a Index scan. How can you force it to use that
> Index..
> 
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
> 
> select * from botched_table where content = 200::integer

EXPLAIN ANALYZE is your friend.
VACUUM [FULL] ANALYZE also.
Try with 200::text
In the end if there is an option for the planner to use the index
but he doesn't, then maybe its not worth it.

> 
> Theo
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com] 
> Sent: Friday, 10 September 2004 4:46 AM
> To: Theo Galanakis; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Isnumeric function?
> 
> 
> Theo,
> 
> > Does anyone have any better suggestions???
> 
> Well, one suggestion would be to take a machete to your application.
> Putting 
> key references and text data in the same column?   Sheesh.
> 
> If that's not an option, in addition to the approach you've taken, you could
> 
> also do a partial index on the appropriate numeric values:
> 
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
> 
> However, this approach may be more/less effective that the segregation 
> approach you've already taken.
> 
> 

-- 
-Achilleus