Re: [GENERAL] temporary indexes?

Jonathan Vanasco wrote: > I couldn't find any mention of this on the archives... > > Have the project maintainers ever considered extending CREATE INDEX to > support "temporary" indexes like CREATE TEMPORARY TABLE? Not sure if you mean something like this:

Re: [GENERAL] temporary indexes?

Jim Nasby writes: > On 10/21/15 3:28 PM, Jonathan Vanasco wrote: >> Transactions and table-locking issues are probably why temporary indexes >> don't exist. > I think it's more that no one has proposed it until now. It probably > wouldn't be terribly hard to add

Re: [GENERAL] temporary indexes?

Jim Nasby writes: > On 10/22/15 12:36 PM, Tom Lane wrote: >> Uh, why would you do that? You'd be throwing away one of the principal >> performance advantages of temp tables. > Actually, it depends on what behavior you'd expect from a temporary > index. If it was only

Re: [GENERAL] temporary indexes?

On 10/22/15 12:36 PM, Tom Lane wrote: Jim Nasby writes: On 10/21/15 3:28 PM, Jonathan Vanasco wrote: Transactions and table-locking issues are probably why temporary indexes don't exist. I think it's more that no one has proposed it until now. It probably

Re: [GENERAL] temporary indexes?

On 21-10-15 20:43, Jonathan Vanasco wrote: I couldn't find any mention of this on the archives... Have the project maintainers ever considered extending CREATE INDEX to support "temporary" indexes like CREATE TEMPORARY TABLE? When creating temporary tables for analytics/reporting, I've

Re: [GENERAL] temporary indexes?

On Oct 22, 2015, at 2:08 PM, Tom Lane wrote: > FWIW, I don't find much attraction in the idea of building an index for > use by a single query. There basically isn't any scenario where that's > going to beat running a plan that doesn't require the index. The value of > an index is generally to

Re: [GENERAL] temporary indexes?

On 10/22/15 3:15 PM, Jonathan Vanasco wrote: On Oct 22, 2015, at 8:17 AM, vincent elschot wrote: Do you mean creating a temporary index on a non-temporary table to speed up the queries that fills the temporary table? One of the use-cases is speeding up inserts on create, but another is for

Re: [GENERAL] temporary indexes?

On Oct 22, 2015, at 5:04 PM, Jim Nasby wrote: > > What % of execution time is spent creating those indexes? Or is that factored > into the 1000%? Also, could your analysis queries be run in a REPEATABLE READ > transaction (meaning that once the transaction starts it doesn't get any new >

Re: [GENERAL] temporary indexes?

On 10/21/15 3:28 PM, Jonathan Vanasco wrote: On Oct 21, 2015, at 3:42 PM, Adrian Klaver wrote: I misunderstood then. The only thing I can think of is to wrap in a transaction, though that presents other issues with open transactions and/or errors in the transaction. I just explicitly

Re: [GENERAL] temporary indexes?

On 10/21/2015 11:43 AM, Jonathan Vanasco wrote: I couldn't find any mention of this on the archives... Have the project maintainers ever considered extending CREATE INDEX to support "temporary" indexes like CREATE TEMPORARY TABLE? When creating temporary tables for analytics/reporting, I've

Re: [GENERAL] temporary indexes?

On Wed, Oct 21, 2015 at 11:50 AM, Adrian Klaver wrote: > On 10/21/2015 11:43 AM, Jonathan Vanasco wrote: > >> I couldn't find any mention of this on the archives... >> >> Have the project maintainers ever considered extending CREATE INDEX to >> support "temporary"

[GENERAL] temporary indexes?

I couldn't find any mention of this on the archives... Have the project maintainers ever considered extending CREATE INDEX to support "temporary" indexes like CREATE TEMPORARY TABLE? When creating temporary tables for analytics/reporting, I've noticed that I often need to create (then drop)

Re: [GENERAL] temporary indexes?

On 10/21/2015 12:27 PM, Jonathan Vanasco wrote: On Oct 21, 2015, at 2:59 PM, Jeff Janes wrote: I think he means more like: create temporary table temp_test(id int, fld_1 varchar); create temporary index on permanent_table (fld_1); select something from temp_test join permanent_table using

Re: [GENERAL] temporary indexes?

On Oct 21, 2015, at 3:42 PM, Adrian Klaver wrote: > I misunderstood then. The only thing I can think of is to wrap in a > transaction, though that presents other issues with open transactions and/or > errors in the transaction. I just explicitly drop. The convenience of an auto-drop would be

Re: [GENERAL] temporary indexes?

On Oct 21, 2015, at 2:59 PM, Jeff Janes wrote: > I think he means more like: > > create temporary table temp_test(id int, fld_1 varchar); > create temporary index on permanent_table (fld_1); > > select something from temp_test join permanent_table using (fld_1) where a=b; > select

Re: [GENERAL] temporary indexes?

lt;adrian.kla...@aklaver.com> Date: 10/21/2015 14:50 (GMT-05:00) To: Jonathan Vanasco <postg...@2xlp.com>, PostgreSQL mailing lists <pgsql-general@postgresql.org> Subject: Re: [GENERAL] temporary indexes? On 10/21/2015 11:43 AM, Jonathan Vanasco wrote: > I couldn't find any mention of t

Re: [GENERAL] temporary indexes?

On 10/21/2015 01:28 PM, Jonathan Vanasco wrote: On Oct 21, 2015, at 3:42 PM, Adrian Klaver wrote: I misunderstood then. The only thing I can think of is to wrap in a transaction, though that presents other issues with open transactions and/or errors in the transaction. I just explicitly