From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: Temporary indexes |
Date: | 2000-04-06 18:05:56 |
Message-ID: | 27917.955044356@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2000-04-06 18:06:50 | Re: pg_dumplo, thanks :) (fwd) |
Previous Message | Bruce Momjian | 2000-04-06 18:05:55 | Re: pg_dumplo, thanks :) (fwd) |