Обсуждение: Recasting data type
Hi All, Thanks for the help on the 'lumper' vs. 'splitter' question. I asked my practice database: select count (*) from titles where price > 20 It responded: PostgreSQL said: ERROR: Unable to identify an operator '>' for types 'money' and 'int4' You will have to retype this query using an explicit cast I tried variations on this theme and got nowhere: select count (*) from titles cast(price as float8) where price > 20 Thanks, Conrad +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Stay informed about the latest changes at masks.org, sign up for the eNewsletter at: <http://www.masks.org/about/form_mail/newsletter_subscribe.html> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ masks.org survives on your donations. Please help by mailing a tax-deductible donation to: masks.org P.O. Box 12089 Olympia WA 98508 USA Our website is at: <http://www.masks.org/>
On Wed, Feb 07, 2001 at 11:31:43AM -0800, Conrad Schuler wrote: > Hi All, > > Thanks for the help on the 'lumper' vs. 'splitter' question. > > I asked my practice database: > > select count (*) from titles where price > 20 > > It responded: > > PostgreSQL said: ERROR: Unable to identify an operator '>' for types > 'money' and 'int4' You will have to retype this query using an explicit cast > > I tried variations on this theme and got nowhere: > select count (*) from titles cast(price as float8) where price > 20 Why don't you try: select count(*) from titles where price > 20.00 It seems that the explicit decimal should automatically cause the cast. I'm not positive it will work, but it's worth a try. -- Dr. David C. Merrill http://www.lupercalia.net Linux Documentation Project david@lupercalia.net Collection Editor & Coordinator http://www.linuxdoc.org Finger me for my public key One seldom sees a monument to a committee.
Hello. I have a table with, say, three fields. The first is the ID (integer, unique, primary index). The second is an optional index that sometimes can be blank, or even duplicated, a varchar(13). The third one is the data I want to retrieve, it has no importance in this problem. I need to access sometimes by the ID and sometimes by the secondary key, so I made an index using CREATE INDEX my_index_name ON MY_TABLE(my_secondary_id); When I select using the primary key, obviously uses the index created by default. When I select using the secondary key, it EXPLAINs me that it would use sequencial scan instead of the index I created, thus this search becomes extremely slow. Why the engine would ignore the index? Do I have to make it "available" in some way I am not aware of? I VACUUM ANALYZEd already, and nothing changed. I thank you in advance any help. Leandro.
Leandro Fanzone <leandro@hasar.com> writes:
> Hello. I have a table with, say, three fields. The first is the ID
> (integer, unique, primary index). The second is an optional index that
> sometimes can be blank, or even duplicated, a varchar(13). The third
> one is the data I want to retrieve, it has no importance in this
> problem. I need to access sometimes by the ID and sometimes by the
> secondary key, so I made an index using
> CREATE INDEX my_index_name ON MY_TABLE(my_secondary_id);
> When I select using the primary key, obviously uses the index created
> by default. When I select using the secondary key, it EXPLAINs me
> that it would use sequencial scan instead of the index I created, thus
> this search becomes extremely slow. Why the engine would ignore the
> index?
Probably because it thinks the indexscan would not be very selective.
An indexscan that has to visit more than a few percent of the rows in
a table is actually slower than a seqscan, typically, and so the planner
won't choose an indexscan if it thinks a large number of rows will be
scanned.
If the secondary column has a lot of "dummy" values as you imply, it's
important to be sure that the dummy values are NULLs, not any other
randomly chosen value; otherwise the dummies will skew the VACUUM
ANALYZE statistics so that the planner will think the column contains
only a few oft-repeated values. If it thinks that, then it's likely
to avoid indexscans.
If you need more help, please send along the exact output of EXPLAIN
for your problem query, also the EXPLAIN result after doing "SET
enable_seqscan TO OFF", and the results of
select attname,attdisbursion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'YOURTABLENAMEHERE';
so we can see what statistics the planner is looking at and what its cost
estimates are. (NOTE: these directions assume you are running 7.0.*)
regards, tom lane
You were right: I changed those dummy values from "0" to NULL, and now it chooses to use the index. The percentage of dummy values was near 40%. Thank you very much. Leandro Fanzone. Tom Lane wrote: > Leandro Fanzone <leandro@hasar.com> writes: > > Hello. I have a table with, say, three fields. The first is the ID > > (integer, unique, primary index). The second is an optional index that > > sometimes can be blank, or even duplicated, a varchar(13). The third > > one is the data I want to retrieve, it has no importance in this > > problem. I need to access sometimes by the ID and sometimes by the > > secondary key, so I made an index using > > > CREATE INDEX my_index_name ON MY_TABLE(my_secondary_id); > > > When I select using the primary key, obviously uses the index created > > by default. When I select using the secondary key, it EXPLAINs me > > that it would use sequencial scan instead of the index I created, thus > > this search becomes extremely slow. Why the engine would ignore the > > index? > > Probably because it thinks the indexscan would not be very selective. > An indexscan that has to visit more than a few percent of the rows in > a table is actually slower than a seqscan, typically, and so the planner > won't choose an indexscan if it thinks a large number of rows will be > scanned. > > If the secondary column has a lot of "dummy" values as you imply, it's > important to be sure that the dummy values are NULLs, not any other > randomly chosen value; otherwise the dummies will skew the VACUUM > ANALYZE statistics so that the planner will think the column contains > only a few oft-repeated values. If it thinks that, then it's likely > to avoid indexscans. > > If you need more help, please send along the exact output of EXPLAIN > for your problem query, also the EXPLAIN result after doing "SET > enable_seqscan TO OFF", and the results of > > select attname,attdisbursion,s.* > from pg_statistic s, pg_attribute a, pg_class c > where starelid = c.oid and attrelid = c.oid and staattnum = attnum > and relname = 'YOURTABLENAMEHERE'; > > so we can see what statistics the planner is looking at and what its cost > estimates are. (NOTE: these directions assume you are running 7.0.*) > > regards, tom lane