From: Bruno Wolff III <bruno@wolff.to>
To: Chris Hoover <revoohc@sermonaudio.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Use of subquery causes seq scan???
Date: Tue, 20 Apr 2004 12:56:32 -0500
Message-ID: <20040420175632.GB11203@wolff.to> (raw)
In-Reply-To: <200404201020.05699.revoohc@sermonaudio.com>
References: <200404151248.16067.jeremy.guthrie@berbee.com>
<200404201020.05699.revoohc@sermonaudio.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruno@wolff.to, revoohc@sermonaudio.com
Subject: Re: Use of subquery causes seq scan???
In-Reply-To: <20040420175632.GB11203@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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