Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] Re: [HACKERS] changes in 6.4

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: maillist(at)candle(dot)pha(dot)pa(dot)us (Bruce Momjian)
Cc: daveh(at)insightdist(dot)com, hannu(at)trust(dot)ee, pgsql-interfaces(at)postgreSQL(dot)org, hackers(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] Re: [HACKERS] changes in 6.4
Date: 1998-07-16 01:22:33
Message-ID: 199807160122.VAA08917@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackerspgsql-interfaces
> > The results are profound.    Queries that used to scan tables because of the
> OK, I have an idea.  Just today, we allow:
> 
> 	select *
> 	from tab1
> 	where val in (
> 		select x from tab2
> 		union
> 		select y from tab3
> 	)
> 
> How about if instead of doing:
> 
> 	select * from tab1 where val = 3
> 	union
> 	select * from tab1 where val = 4
> 	...
> 
> you change it to:
> 	
> 	select * from tab1 where val in (
> 		select 3
> 		union
> 		select 4
> 	)

OK, I just ran some test, and it does not look good:

---------------------------------------------------------------------------

son_db=> explain select mmatter from matter where mmatter =  'A01-001';
NOTICE:  QUERY PLAN:

Index Scan using i_matt2 on matter  (cost=2.05 size=1 width=12)

EXPLAIN

son_db=> explain select mmatter from matter where mmatter in (select 'A01-001');
NOTICE:  QUERY PLAN:

Seq Scan on matter  (cost=512.20 size=1001 width=12)
  SubPlan
    ->  Result  (cost=0.00 size=0 width=0)

EXPLAIN

---------------------------------------------------------------------------

Turns out indexes are not used in outer queries of subselects.  Not sure
why.  Vadim?



-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

In response to

pgsql-hackers by date

Next:From: Maarten BoekholdDate: 1998-07-16 07:45:18
Subject: Re: [INTERFACES] Re: [HACKERS] changes in 6.4
Previous:From: Vadim MikheevDate: 1998-07-15 23:46:25
Subject: proposals for LLL, part 1

pgsql-interfaces by date

Next:From: JeffDate: 1998-07-16 05:22:48
Subject: Problems with CGI to read database.
Previous:From: Bruce MomjianDate: 1998-07-15 23:39:05
Subject: Re: [INTERFACES] Re: [HACKERS] changes in 6.4

pgsql-general by date

Next:From: Robert CampbellDate: 1998-07-16 04:52:53
Subject: null as a value
Previous:From: 1214Date: 1998-07-15 23:41:47
Subject: Mega-Cash Wants To Advertise On Your Site!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group