Re: Isnumeric function? - Mailing list pgsql-sql

Greg Stark <gsstark@MIT.EDU> writes:

> 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.

Well this is weird. I tried to come up with a cleaner way to arrange this than
the view I described before using a function. But postgres isn't using the
partial index when it seems it ought to be available.

When I say it has to match "pretty closely" in this case I think it would have
to match exactly, however in the case of simple range operators postgres knows
how to figure out implications. Ie, "where a>1" should use a partial index
built on "where a>0".

slo=> create table test (a integer);
CREATE TABLE
slo=> create index idx_text on test (a) where a > 0;
CREATE INDEX
slo=> explain select * from test where a > 0;                              QUERY PLAN                               
------------------------------------------------------------------------Index Scan using idx_text on test
(cost=0.00..17.50rows=334 width=4)  Index Cond: (a > 0)
 
(2 rows)

slo=> explain select * from test where a > 1;                              QUERY PLAN                               
------------------------------------------------------------------------Index Scan using idx_text on test
(cost=0.00..17.50rows=334 width=4)  Index Cond: (a > 1)
 
(2 rows)



That's all well and good. But when I tried to make a version of your situation
that used a function I found it doesn't work so well with functional indexes:



slo=> create function test(integer) returns integer as 'select $1' language plpgsql immutable;
CREATE FUNCTION
slo=> create index idx_test_2 on test (test(a)) where test(a) > 0;
CREATE INDEX
slo=> explain select test(a) from test where test(a) > 0;                               QUERY PLAN
         
 
--------------------------------------------------------------------------Index Scan using idx_test_2 on test
(cost=0.00..19.17rows=334 width=4)  Index Cond: (test(a) > 0)
 
(2 rows)

slo=> explain select test(a) from test where test(a) > 1;                     QUERY PLAN                       
-------------------------------------------------------Seq Scan on test  (cost=0.00..25.84 rows=334 width=4)  Filter:
(test(a)> 1)
 
(2 rows)


I can't figure out why this is happening. I would think it has something to do
with the lack of statistics on functional indexes except a) none of the tables
is analyzed anyways and b) the estimated row count is the same anyways.


-- 
greg