Postgres Pro
Downloads
Home   >   mailing lists

Thread: Temporary indexes

Temporary indexes

From
Bruce Momjian
Date:
Do we have temporary indexes?
test=> CREATE  TABLE temptest(col INTEGER);CREATEtest=> create index ix on temptest (col);CREATEtest=> CREATE TEMP
TABLEmasktest (col INTEGER);CREATEtest=> create index ix on temptest (col);ERROR:  Cannot create index: 'ix' already
exists

Seems we don't.  Should I add it to the TODO list?

--  Bruce Momjian                        |  http://www.op.net/~candle 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,
Pennsylvania19026

Re: Temporary indexes

From
Bruce Momjian
Date:
> Do we have temporary indexes?
> 
>     test=> CREATE  TABLE temptest(col INTEGER);
>     CREATE
>     test=> create index ix on temptest (col);
>     CREATE
>     test=> CREATE TEMP TABLE masktest (col INTEGER);
>     CREATE
>     test=> create index ix on temptest (col);
>     ERROR:  Cannot create index: 'ix' already exists
> 
> Seems we don't.  Should I add it to the TODO list?

Oh, I see now, I was creating the index on temptest, not masktest. 
Sorry.  It works fine.

--  Bruce Momjian                        |  http://www.op.net/~candle 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,
Pennsylvania19026

Re: Temporary indexes

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Do we have temporary indexes?
>     test=> CREATE  TABLE temptest(col INTEGER);
>     CREATE
>     test=> create index ix on temptest (col);
>     CREATE
>     test=> CREATE TEMP TABLE masktest (col INTEGER);
>     CREATE
>     test=> create index ix on temptest (col);
>     ERROR:  Cannot create index: 'ix' already exists

> Seems we don't.  Should I add it to the TODO list?

It seems to work when you use the right table names ;-)

regression=# create table foo (f1 int);
CREATE
regression=# create index foo_i on foo(f1);
CREATE
regression=# create temp table foo (f1t int);
CREATE
regression=# create index foo_i on foo(f1);
ERROR:  DefineIndex: attribute "f1" not found
regression=# create index foo_i on foo(f1t);
CREATE
regression=# explain select * from foo where f1t = 33;
NOTICE:  QUERY PLAN:

Index Scan using foo_i on foo  (cost=0.00..8.14 rows=10 width=4)

EXPLAIN
-- reconnect to drop temp tables
regression=# \connect regression
You are now connected to database regression.
regression=# explain select * from foo where f1t = 33;
ERROR:  Attribute 'f1t' not found
regression=# explain select * from foo where f1 = 33;
NOTICE:  QUERY PLAN:

Index Scan using foo_i on foo  (cost=0.00..8.14 rows=10 width=4)

EXPLAIN
regression=#


I do observe a minor glitch though, which is that psql's \d command
doesn't pay attention to temp-table aliases:

regression=# \d foo         Table "foo"Attribute |  Type   | Modifier
-----------+---------+----------f1        | integer |
Index: foo_i

regression=#
regression=# create temp table foo (f1t int);
CREATE
regression=# \d foo         Table "foo"Attribute |  Type   | Modifier
-----------+---------+----------f1        | integer |
Index: foo_i

I should be shown the temp table here, but I'm not.
        regards, tom lane