From: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at>
Subject: CREATE INDEX question
Date: Thu, 26 Dec 2002 13:24:12 +0100
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 52
Message-ID: <3E0AF4EC.2000102@cybertec.at>
Reply-To: hs@cybertec.at
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020826
To: pgsql-sql@postgresql.org

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>