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=3333333 width=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>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
