From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
Cc: 'Josh Berkus' <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Isnumeric function?
Date: Fri, 10 Sep 2004 08:39:33 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0409100837300.3763-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <82E30406384FFB44AFD1012BAB230B55037D0591@shiva.au.lpint.net>
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
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-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, Theo.Galanakis@lonelyplanet.com.au, josh@agliodbs.com
Subject: Re: Isnumeric function?
In-Reply-To: <Pine.LNX.4.44.0409100837300.3763-100000@matrix.gatewaynet.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