Re: Subselects and Indices
От | Andre Schubert |
---|---|
Тема | Re: Subselects and Indices |
Дата | |
Msg-id | 3C4D0D27.5422D407@kabeljournal.de обсуждение исходный текст |
Ответ на | Subselects and Indices (Andre Schubert <andre.schubert@kabeljournal.de>) |
Список | pgsql-admin |
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"
В списке pgsql-admin по дате отправления: