Thread: Performance question related with temporary tables

"Vilson farias" <vilson.farias@digitro.com.br> writes:
>   I've been testing some queries and I have a question : Is there a way to =
> put a index in a temporary table?

You just do it.

test72=# create temp table foo (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
test72=# explain select * from foo where f1 = 42;
NOTICE:  QUERY PLAN:
Index Scan using foo_pkey on foo  (cost=0.00..4.82 rows=1 width=4)

or if you prefer

test72=# drop table foo;
DROP
test72=# create temp table foo (f1 int);
CREATE
test72=# create index fooi on foo(f1);
CREATE
test72=# explain select * from foo where f1 = 42;
NOTICE:  QUERY PLAN:
Index Scan using fooi on foo  (cost=0.00..17.07 rows=5 width=4)

It's not any different from working with a permanent table...

            regards, tom lane

> You just do it.
Great, it's so simple... thanks Tom.

I just have another two questions :

Is the index automaticly removed when connection is closed?

The index is visible only in the actual session, am I right?

Best Regards from Brazil!!

"Vilson farias" <vilson.farias@digitro.com.br> writes:
>> You just do it.

> Great, it's so simple... thanks Tom.

> I just have another two questions :

> Is the index automaticly removed when connection is closed?
> The index is visible only in the actual session, am I right?

Yup.  An index on a temp table is temp in just the same way as the temp
table itself.  There's no "TEMP" option in CREATE INDEX, it's just done
that way for you.  (Any toast table needed is also handled correctly,
although the mechanism is slightly different.)

            regards, tom lane