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>
