From: Hans-Jürgen Schönig <hs@cybertec.at>
To: pgsql-sql@postgresql.org
Subject: CREATE INDEX question
Date: Thu, 26 Dec 2002 13:24:12 +0100
Message-ID: <3E0AF4EC.2000102@cybertec.at> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hs@cybertec.at
Subject: Re: CREATE INDEX question
In-Reply-To: <3E0AF4EC.2000102@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox