From: Greg Stark <gsstark@mit.edu>
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
Cc: 'Josh Berkus' <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Isnumeric function?
Date: 10 Sep 2004 02:43:46 -0400
Message-ID: <871xhatyhp.fsf@stark.xeocode.com> (raw)
In-Reply-To: <82E30406384FFB44AFD1012BAB230B55037D0591@shiva.au.lpint.net>
References: <82E30406384FFB44AFD1012BAB230B55037D0591@shiva.au.lpint.net>
Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
> 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
You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
to match the clause in the partial index pretty closely.
perhaps you would find it convenient to make a view of
select * from botched_table where content ~ '^[0-9]{1,9}$'
and then just always select these values from that view.
Also the "::integer" is useless. It actually gets cast to text here anyways.
The index is on the text contents of the content column.
You might consider making the index a functional index on content::integer
instead. I suspect that would be faster and smaller than an index on the text
version of content:
slo=> create table botched_table (content text);
CREATE TABLE
slo=> create index idx_botched_table on botched_table ((content::integer)) where content ~ '^[0-9]{1,9}$';
CREATE INDEX
slo=> create view botched_view as (select content::integer as content_id, * from botched_table where content ~ '^[0-9]{1,9}$');
CREATE VIEW
slo=> explain select * from botched_view where content_id = 1;
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using idx_botched_table on botched_table (cost=0.00..3.72 rows=3 width=32)
Index Cond: ((content)::integer = 1)
Filter: (content ~ '^[0-9]{1,9}$'::text)
(3 rows)
--
greg
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: gsstark@mit.edu, Theo.Galanakis@lonelyplanet.com.au, josh@agliodbs.com
Subject: Re: Isnumeric function?
In-Reply-To: <871xhatyhp.fsf@stark.xeocode.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