Postgres Pro
Downloads
Home   >   mailing lists

Thread: Any way to 'analyze' indexes to get updated sizes?

Any way to 'analyze' indexes to get updated sizes?

From
"Jeremy M. Guthrie"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is there a way to analyze indexes to provide updated sizes?  Is a vacuum the
only way to determine the size of an index?  Analyze updates the stats so I
can see table space sizes but I cannot find an alternative to vacuum for
indexes.

- --

- --------------------------------------------------
Jeremy M. Guthrie        jeremy.guthrie@berbee.com
Network Engineer               Phone: 608-298-1061
Berbee                           Fax: 608-288-3007
5520 Research Park Drive         NOC: 608-298-1102
Madison, WI 53711
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAfsreqtjaBHGZBeURAm3+AJ9F34SESTf8i/oEuKvKfXoh+NcOxwCcDcM9
HP5LHM3Qidb4wa2/rW5H0cI=
=mJCz
-----END PGP SIGNATURE-----

Use of subquery causes seq scan???

From
"Chris Hoover"
Date:
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

Re: Use of subquery causes seq scan???

From
Tom Lane
Date:
"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

Re: Use of subquery causes seq scan???

From
Bruno Wolff III
Date:
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.