Skip site navigation (1) Skip section navigation (2)

Re: temporary indexes

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: temporary indexes
Date: 2006-02-28 16:45:15
Message-ID: 20060228164515.GS82012@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Tue, Feb 28, 2006 at 09:44:08AM -0600, Kevin Grittner wrote:
> It struck me that it would be outstanding if the planner could
> recognize this sort of situation, and build a temporary index based on
> the snapshot of the data visible to the transaction.  It seems to me
> that the obvious downside of this would be the explosion in the number
> of permutations the planner would need to examine -- based not just on
> what indexes ARE there, but which ones it could build.  At a minimum,
> there would need to be a cost threshold below which it would not even
> consider the option.  (In this case, as long as the optimizer spent less
> than 13 hours and 50 minutes considering its options, we would have come
> out ahead.)

FWIW, Sybase supported something similar a long time ago. It had the
ability to build a temporary 'clustered table' (think index organized
table)  when there was enough benefit to do so. This is actually
much easier to make happen inside a transaction for us, because we don't
need to keep visibility information around. There's probably also some
index metadata that could be done away with. Perhaps the materialize
node could be made to allow this.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-02-28 16:52:11
Subject: Re: [PERFORM] temporary indexes
Previous:From: Kevin GrittnerDate: 2006-02-28 15:44:08
Subject: temporary indexes

pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2006-02-28 16:45:27
Subject: Re: character encoding in StartupMessage
Previous:From: Sokolov YuraDate: 2006-02-28 16:44:41
Subject: Re: Dead Space Map

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group