Thread: temp table indexes
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
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 loadedfrom 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 vacuumthe temp table once loaded. I have tired vacuuming the table from within the plpgsql proc, but that seems to causepostgres 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
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. > > > > 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
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