temp table indexes

Read FAQ item 4.8 --- new version on web site. It explains index is not
always the best.

---------------------------------------------------------------------------

Andrew Bartley wrote:

Hi,

We are using Postgres 7.1.3.

I am trying to get postgres to use an index that is created on a temp table. The temp table and index is created and loaded from within a plpgsql proc. The select I am trying to force a keyed select on, is also in the same proc.

I have tired SET ENABLE_SEQSCAN TO Off But it still does table scans.

If I create the same temp table from an odbc driven windows sql app like WinSQL lite, I can force an index read if I vacuum the temp table once loaded. I have tired vacuuming the table from within the plpgsql proc, but that seems to cause postgres to shut down.

Can anyone help me here?

Thanks

Andrew

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Andrew Bartley wrote:

The testing I have already done, an index is by far better in this
circumstance.

If it is a non-temp table with the same data and VACUUM ANALYZE, does it
use an index?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

The testing I have already done, an index is by far better in this
circumstance.

Thanks

Andrew
----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Andrew Bartley" <abartley@evolvosystems.com>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, March 19, 2002 4:19 PM
Subject: Re: [GENERAL] temp table indexes

Read FAQ item 4.8 --- new version on web site. It explains index is not
always the best.

--------------------------------------------------------------------------

-

Andrew Bartley wrote:

Hi,

We are using Postgres 7.1.3.

I am trying to get postgres to use an index that is created on a temp

table. The temp table and index is created and loaded from within a plpgsql
proc. The select I am trying to force a keyed select on, is also in the
same proc.

I have tired SET ENABLE_SEQSCAN TO Off But it still does table scans.

If I create the same temp table from an odbc driven windows sql app like

WinSQL lite, I can force an index read if I vacuum the temp table once
loaded. I have tired vacuuming the table from within the plpgsql proc, but
that seems to cause postgres to shut down.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Andrew Bartley wrote:

The testing I have already done, an index is by far better in this
circumstance.

If it is a non-temp table with the same data and VACUUM ANALYZE, does it
use an index?

The default stats values used in the absence of any VACUUM are supposed
to yield an index search. Temp-ness is irrelevant. For example:

regression=# create temp table foo (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
regression=# explain select * from foo where f1 = 42;
INFO: QUERY PLAN:

Index Scan using foo_pkey on foo (cost=0.00..4.82 rows=1 width=4)
indxqual: (f1 = 42)

EXPLAIN

I'd be interested to see the details of Andrew's example where this
does not happen.

regards, tom lane