pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTemporary tables and indexes
5+ messages / 4 participants
[nested] [flat]
* Temporary tables and indexes
@ 2002-10-08 07:28 Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ludwig Lim @ 2002-10-08 07:28 UTC (permalink / raw)
To: pgsql-sql
Hi :
Are the indices of a temporary table automatically
"dropped" together its corresponding temporary table
after a database session?
ludwig.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Temporary tables and indexes
@ 2002-10-08 15:36 Josh Berkus <josh@agliodbs.com>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2002-10-08 15:36 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; pgsql-sql
Ludwig,
> Are the indices of a temporary table automatically
> "dropped" together its corresponding temporary table
> after a database session?
I'm kind of surprised that it's possible to index a temporary table.
There's not much point in doing so.
Yes, the indexes would be dropped as well.
-Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Temporary tables and indexes
@ 2002-10-08 16:13 Ian Harding <ianh@tpchd.org>
0 siblings, 0 replies; 5+ messages in thread
From: Ian Harding @ 2002-10-08 16:13 UTC (permalink / raw)
To: pgsql-sql
Sure there is! There are queries that benefit from having a temporary table created for a subquery and the temporary table indexed before the join. Since we can't easily return result sets from functions yet, it's not probably used that much, but from within a function, I can see why you might want to do this from time to time.
Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
iharding@tpchd.org
WWSD - What Would Scooby Doo?
>>> "Josh Berkus" <josh@agliodbs.com> 10/08/02 08:36AM >>>
Ludwig,
> Are the indices of a temporary table automatically
> "dropped" together its corresponding temporary table
> after a database session?
I'm kind of surprised that it's possible to index a temporary table.
There's not much point in doing so.
Yes, the indexes would be dropped as well.
-Josh Berkus
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Temporary tables and indexes
@ 2002-10-09 04:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2002-10-09 04:45 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Ludwig Lim <lud_nowhere_man@yahoo.com>; pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> I'm kind of surprised that it's possible to index a temporary table.
> There's not much point in doing so.
Why not? You seem to be equating "temporary" with "small", but I don't
see why that must be so.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Temporary tables and indexes
@ 2002-10-09 04:49 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2002-10-09 04:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: Ludwig Lim <lud_nowhere_man@yahoo.com>; pgsql-sql
Tom,
> > I'm kind of surprised that it's possible to index a temporary
> table.
> > There's not much point in doing so.
>
> Why not? You seem to be equating "temporary" with "small", but I
> don't
> see why that must be so.
Nah. I'm equating "temporary" with "query twice and throw away", which
isn't necessarily what everyone else does.
BTW, Ludwig, in postgresql versions < 7.2.0, I noticed that sometimes
indexes didn't seem to completely go away (i.e. I would get a name
conflict if I tried to drop & re-create in the same transaction)
without a VACUUM. If you're using 7.1.3, you should upgrade for this
reason.
-Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-10-09 04:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-08 07:28 Temporary tables and indexes Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-10-08 15:36 ` Josh Berkus <josh@agliodbs.com>
2002-10-09 04:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-09 04:49 ` Josh Berkus <josh@agliodbs.com>
2002-10-08 16:13 Re: Temporary tables and indexes Ian Harding <ianh@tpchd.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox