Re: Subselects and Indices

Hi,

thank you for the short explanation of the problem.
I'am trying to rewrite this query into another to get out the
subselect...

thanks as

Brian McCane schrieb:
>
> Your problem is probably type-casting related. The result of the
> sub-select is most likely an ::int32 and doesn't work when compared with
> an ::int8. I could be mistaken, but I have seen something similar here
> when trying to keep my tables smaller using smaller integer fields. You
> might try:
>
> => SELECT * FROM foo WHERE id IN (SELECT 10::int8)
> -or-
> => SELECT * FROM foo WHERE id IN (SELECT 10)::int8
>
> I am not sure if that will work or not. Oops, just tried the second one,
> it returns:
>
> ERROR: Cannot cast type 'bool' to 'int8'
>
> It must be attempting to cast the results of 'id IN (SELECT 10)' to int8.
>
> Also, I would probably advise strongly against this method. On my
> database using the sub-select causes a sequential scan. In the case of
> the database I tested against, the explain said:
>
> => explain select * from foo where uid in (select 10::int8) ;
> NOTICE: QUERY PLAN:
>
> Seq Scan on foo (cost=100000000.00..101091094.85 rows=56251988 width=20)
> SubPlan
> -> Materialize (cost=0.00..0.00 rows=0 width=0)
> -> Result (cost=0.00..0.00 rows=0 width=0)
>
> and a normal 'IN' returns:
>
> => explain select * from foo where uid in (10) ;
> NOTICE: QUERY PLAN:
>
> Index Scan using foo_pkey on foo (cost=0.00..81.17 rows=20 width=20)
>
> I always try to avoid the "IN (SELECT ...)" construct because I have never
> seen a good explain (or fast results) from it. I would try to write your
> query using a NATURAL JOIN or EXISTS to give better performance. My
> drives and system are fairly fast, but checking 56.3M records would take
> quite a while.
>
> - brian
>
> On Mon, 21 Jan 2002, Andre Schubert wrote:
>
> > Hi,
> >
> > i have a simple question on indices and subselects.
> > Lets say i have a table foo with a column id int8 as primary_key bar.
> > If i do a
> >
> > select * from foo where id = 10 then the index bar is used.
> >
> > if i do
> > select * from foo where id in (10,20) then the index is used.
> >
> > but if id do
> > select * from foo where id in ( select 10 ) then the index is not used.
> >
> > Can anyone explain me this. It is very important, because i have to do
> > this subselect.
> >
> > thanks, as
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
> Wm. Brian McCane | Life is full of doors that won't open
> Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
> Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
> Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"