From: | Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | Theo Galanakis <Theo(dot)Galanakis(at)lonelyplanet(dot)com(dot)au> |
Cc: | "'Josh Berkus'" <josh(at)agliodbs(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Isnumeric function? |
Date: | 2004-09-10 05:39:33 |
Message-ID: | Pine.LNX.4.44.0409100837300.3763-100000@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)agliodbs(dot)com]
> Sent: Friday, 10 September 2004 4:46 AM
> To: Theo Galanakis; pgsql-sql(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2004-09-10 06:43:46 | Re: Isnumeric function? |
Previous Message | sad | 2004-09-10 04:48:33 | Re: Isnumeric function? |