Thread: CREATE INDEX question

Is there a way to influence the data type of an index being created?

Some like that would be fine:

CREATE INDEX idx_data2_x ON t_data2(x::int4);


It would be nice to have a workaround for that:


[hs@backup mag]$ time psql -p 5400 test -c "EXPLAIN SELECT * FROM 
t_data1 WHERE id > (SELECT AVG(id) FROM t_data3) "                                QUERY PLAN
-----------------------------------------------------------------------------Seq Scan on t_data1  (cost=0.00..218966.00
rows=3333333width=22)  Filter: ((id)::numeric > $0)  InitPlan    ->  Aggregate  (cost=1887.00..1887.00 rows=1 width=4)
       ->  Seq Scan on t_data3  (cost=0.00..1637.00 rows=100000 width=4)
 
(5 rows)


real    0m0.057s
user    0m0.010s
sys     0m0.000s
[hs@backup mag]$ time psql -p 5400 test -c "EXPLAIN SELECT * FROM 
t_data1 WHERE id > (SELECT AVG(id) FROM t_data3)::int4 "                                     QUERY PLAN
---------------------------------------------------------------------------------------Index Scan using idx_data1_id on
t_data1 (cost=0.00..83623.33 
 
rows=3333333 width=22)  Index Cond: (id > ($0)::integer)  InitPlan    ->  Aggregate  (cost=1887.00..1887.00 rows=1
width=4)         ->  Seq Scan on t_data3  (cost=0.00..1637.00 rows=100000 width=4)
 
(5 rows)


Logically PostgreSQL cannot do an index scan in the first example due to 
a wrong data type.
I could use a serializable transaction to extract the AVG first but it 
would be fine if it could be done in one query. Casting a decimal value 
to integer to make use of the index is definitely not a good solution. 
Changing the data type of the column is not a practical solution as well.

Does anybody have an elegant idea?
I have heard that there are plans to fix this in the future but does 
anybody know a workaround for 7.3.1?
   Hans
<http://kernel.cybertec.at>