Thread: typecast for index

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

John Henderson wrote:
>
> 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.
>
> 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

IIRC, this is a known issue with 'IN', and that the traditional
solution was to replace use of 'IN' with 'EXISTS', i.e.,

    and EXISTS (select * from userdir where user_name = username and
towncode...)

Cheers,
Ed Loehr