From: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | coronach(at)hill-b-073(dot)resnet(dot)purdue(dot)edu (Coronach) |
Cc: | pgsql-hackers(at)postgreSQL(dot)org, vadim(at)sable(dot)krasnoyarsk(dot)su (Vadim B(dot) Mikheev) |
Subject: | Re: [HACKERS] psql nested queries with 2000+ records |
Date: | 1998-03-21 03:38:10 |
Message-ID: | 199803210338.WAA05790@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)hill-b-073(dot)resnet(dot)purdue(dot)edu
>
>
--
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-03-21 03:40:54 | Re: [HACKERS] Re: [PATCHES] patches for 6.2.1p6 |
Previous Message | David Gould | 1998-03-21 01:05:32 | Re: [HACKERS] Re: [PATCHES] patches for 6.2.1p6 |