Re: switching default integer datatype to int8 and "IN (...)"

Thanks Andy - this "solves" the problem(*1) on the first level, where I
know how to quote the params, so they must be evaluated / casted.

But when I get into the subselects, I don't know how to make postgres to
cast / evaluate the results of the subselect, so it again does only
sequential scan.

How do I make postgres cast ( or evaluate? ) the subselect ?, so that
when I do

# explain _the_right_select_quesry_with_subselect_

I want to get (Index Scan):
Index Scan using file_pkey on file (cost=0.00..5.01 rows=1 width=8)
SubPlan
-> Materialize (cost=37209.28..37209.28 rows=9535 width=8)
-> Index Scan using parentid_name_idx on file (cost=0.00..37209.28 rows=9535 width=8)

but now instead I'm getting with this:
# explain select id from file where id in( select id from file where parentid ='355764');

I don't want to get (Seq Scan): (that's what I'm getting now with the above query)
Seq Scan on file (cost=0.00..70956514802.83 rows=953478 width=8)
SubPlan
-> Materialize (cost=37209.28..37209.28 rows=9535 width=8)
-> Index Scan using parentid_name_idx on file (cost=0.00..37209.28 rows=9535 width=8)

What's the right _the_right_select_quesry_with_subselect_ with possibly
several nested subselects ?

Thanks,

John

(*1) PS: I guess the problem is that somehow postgres doesn't know by
default that it should try to "cast" the results of the subselects into
type that it is to be comparing it with. (which is int8). Is there a way
to formulate the query to ask for the cast, perhaps explicitly ? Or is
there a way to set a variable or some other condition which will tell
postgres to perform this cast implicitly ? -- Thanx !

On Thu, 9 Jan 2003, Andrew J. Kopciuch wrote:

> > but a select like this takes ages (looooong time):
> > # select * from file where id = 1921773;
> > id | name
> > -----+----------------
> > 1921777 | icons
> >
>
> I believe the reason is this : the numeric literal is first considered an int4
> becuase it falls within the range of int4 (-2147483648 to +2147483647).
>
> try quoting the literal like this:
>
> # select * from file where id = '1921773';
>
> This forces the literal to be evaluated. If you do an explain on that query
> ... you should see that the query planner uses the index as expected and that
> the condition used on the index is using the literal value cast to a big int.
>
>
> That's just my understanding anyway.
>
>
> Andy

--
-- Gospel of Jesus' kingdom = saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Honza.Vicherek.com/