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

Re: more than one index in a single heap pass?

From: decibel <decibel(at)decibel(dot)org>
To: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
Cc: Glen Parker <glenebob(at)nwlink(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Andrew Dunstan <andrew(at)dunslane(dot)net>
Subject: Re: more than one index in a single heap pass?
Date: 2009-07-17 16:57:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Jul 15, 2009, at 2:52 PM, Dimitri Fontaine wrote:
> Le 15 juil. 09 à 02:01, Glen Parker a écrit :
>> Sounds to me like another reason to separate index definition from  
>> creation.  If an index can be defined but not yet created or  
>> valid, then you could imagine syntax like:
>> DEFINE INDEX blahblah1 ON mytable (some fields);
>> DEFINE INDEX blahblah2 ON mytable (some other fields);
>> [RE]INDEX TABLE mytable;
>> ...provided that REINDEX TABLE could recreate all indexes  
>> simultaneously as you suggest.
> Well to me it sounded much more like:
> And at commit time, PostgreSQL would build all the transaction  
> indexes in one pass over the heap, but as Tom already pointed out,  
> using only 1 CPU. Maybe that'd be a way to limit the overall io  
> bandwidth usage while not consuming too many CPU resources at the  
> same time.
> I mean now we have a choice to either sync scan the table heap on  
> multiple CPU, saving IO but using 1 CPU per index, or to limit CPU  
> to only 1 but then scan the heap once per index. The intermediary  
> option of using 1 CPU while still making a single heap scan sure  
> can be worthwhile to some?

Here's an off-the-wall thought... since most of the CPU time is in  
the sort, what about allowing a backend to fork off dedicated sort  
processes? Aside from building multiple indexes at once, that  
functionality could also be useful in general queries.
Decibel!, aka Jim C. Nasby, Database Architect  decibel(at)decibel(dot)org
Give your computer some brain candy! Team #1828

In response to


pgsql-hackers by date

Next:From: Andrew DunstanDate: 2009-07-17 17:05:45
Subject: commitfest app
Previous:From: Petr JelinekDate: 2009-07-17 16:44:12
Subject: Re: [PATCH] DefaultACLs

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