Re: [HACKERS] psql nested queries with 2000+ records

OK, I think I see the problem.  Indexes are not being used in this case.
Basically for every row in the outer query, you are doing a sequential
scan in the inner table.

Can you try this:

select * into games2 from games;
explain select name from games where name in (select name from games2 where
name like 'A%');


I would like to know if the use of the same table in the subquery is
causing the problem.  I assume you have run vacuum and vacuum analyze.


>
> At 12:24 PM 3/20/98 -0500, Bruce Momjian wrote:
>
> >Very strange.  15+ minutes?  Wow, that is terrible, even longer than a
> >sequential scan of the table.  Try EXPLAIN and tell us what it says in
> >the two cases.
>
> This is within the 2717 record database.
>
> explain select name from games where name in (select name from games where
> name like 'A%');
>
> NOTICE:  QUERY PLAN:
>
> Seq Scan on games  (cost=207.95 size=446 width=12)
>   SubPlan
>     ->  Seq Scan on games  (cost=207.95 size=1 width=12)
>
> NOTICE:  QUERY PLAN:
>
> Seq Scan on games  (cost=207.95 size=446 width=12)
>   SubPlan
>     ->  Seq Scan on games  (cost=207.95 size=1 width=12)
>
> EXPLAIN
>
> This is within the 24 record database of the same type of data
>
> explain select name from games where name in (select name from game
> s where name like 'A%');
> NOTICE:  QUERY PLAN:
>
> Seq Scan on games  (cost=207.95 size=446 width=12)
>   SubPlan
>     ->  Seq Scan on games  (cost=207.95 size=1 width=12)
>
> NOTICE:  QUERY PLAN:
>
> Seq Scan on games  (cost=207.95 size=446 width=12)
>   SubPlan
>     ->  Seq Scan on games  (cost=207.95 size=1 width=12)
>
> EXPLAIN
>
> Obviously, I get the same information, any suggestions?
>
> -Coronach@hill-b-073.resnet.purdue.edu
>
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.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)