typecast for index

From: "John Henderson" <jrh(at)is(dot)com(dot)fj>
To: <pgsql-general(at)hub(dot)org>
Subject: typecast for index
Date: 2000-02-14 05:59:23
Message-ID: 016901bf76b0$a89f5840$ea7c3eca@john.is.com.fj
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi y'all,

This is what I want to do:
select user_name, date_trunc('minutes',sum(is_roundup_01(sess_time)))
from sessions_feb2000_homer s where
date_part('epoch',start)>'949316399' and
date_part('epoch',start)<'951822000' and
prob here> user_name IN (select username from userdir
where towncode = 'LAB')
group by user_name
order by user_name;

The problem is that user_name = 'soandso' will use an index scan
but user_name IN (select ...) uses a sequential scan.

I have tried user_name::text, username::text and subselect()::text and get
told
"ERROR: Function 'text(text)' does not exist
Unable to identify a function which satisfies the given argument
types
You will have to retype your query using explicit typecasts"

Fair enuf but I though xxx::text *was* a typecast.

Any advice on how to force this to use an index?
Yes the index exists, yes it has been vaccuumed. This is PG 6.5.3

Since we are on the subject any advice on why text is less preferred than
varchar?

John Henderson

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Yury Don 2000-02-14 07:52:25
Previous Message Ed Loehr 2000-02-14 05:33:18 Re: [GENERAL] typecast for index