pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Use of subquery causes seq scan???
3+ messages / 3 participants
[nested] [flat]

* Use of subquery causes seq scan???
@ 2004-04-20 14:20 Chris Hoover <revoohc@sermonaudio.com>
  2004-04-20 17:56 ` Re: Use of subquery causes seq scan??? Bruno Wolff III <bruno@wolff.to>
  2004-04-20 21:58 ` Re: Use of subquery causes seq scan??? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Chris Hoover @ 2004-04-20 14:20 UTC (permalink / raw)
  To: pgsql-performance

I need some help.  I have a query that refuses to use the provided index and 
is always sequentially scanning causing me large performance headaches.  Here 
is the basic situation:

Table A:
inv_num int
type		char
.
.
.
pkey (inv_num, type)
indx(inv_num)

Table B (has the same primary key)

Select *
from table a
where inv_num in (select inv_num from table b where ....)

Doing this causes sequential scans of both tables.  If I do a set 
enable_seqscan to false before the query, I get an index scan of table b but 
still seq scan table a. 

Is there anyway to force table a to use this index (or another) and not 
sequentially scan the table?

I'm running 7.3.4 on RedHat EL 2.1.

Thanks,

Chris




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Use of subquery causes seq scan???
  2004-04-20 14:20 Use of subquery causes seq scan??? Chris Hoover <revoohc@sermonaudio.com>
@ 2004-04-20 17:56 ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 3+ messages in thread

From: Bruno Wolff III @ 2004-04-20 17:56 UTC (permalink / raw)
  To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: pgsql-performance

Please don't reply to messages to start new threads.

On Tue, Apr 20, 2004 at 10:20:05 -0400,
  Chris Hoover <revoohc@sermonaudio.com> wrote:
> I need some help.  I have a query that refuses to use the provided index and 
> is always sequentially scanning causing me large performance headaches.  Here 
> is the basic situation:
> 
> Table A:
> inv_num int
> type		char
> .
> .
> .
> pkey (inv_num, type)
> indx(inv_num)
> 
> Table B (has the same primary key)
> 
> Select *
> from table a
> where inv_num in (select inv_num from table b where ....)
> 
> Doing this causes sequential scans of both tables.  If I do a set 
> enable_seqscan to false before the query, I get an index scan of table b but 
> still seq scan table a. 
> 
> Is there anyway to force table a to use this index (or another) and not 
> sequentially scan the table?
> 
> I'm running 7.3.4 on RedHat EL 2.1.

IN was slow in 7.3.x and before. The query will probably run much better
as is in 7.4 and above. In 7.3 you want to rewrite it as a join or using
EXISTS.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Use of subquery causes seq scan???
  2004-04-20 14:20 Use of subquery causes seq scan??? Chris Hoover <revoohc@sermonaudio.com>
@ 2004-04-20 21:58 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2004-04-20 21:58 UTC (permalink / raw)
  To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: pgsql-performance

"Chris Hoover" <revoohc@sermonaudio.com> writes:
> Select *
> from table a
> where inv_num in (select inv_num from table b where ....)

> I'm running 7.3.4 on RedHat EL 2.1.

IN (SELECT) constructs pretty well suck in PG releases before 7.4.
Update, or consult the FAQ about rewriting into an EXISTS form.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-04-20 21:58 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-20 14:20 Use of subquery causes seq scan??? Chris Hoover <revoohc@sermonaudio.com>
2004-04-20 17:56 ` Bruno Wolff III <bruno@wolff.to>
2004-04-20 21:58 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox