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: C27DBC18-9BC8-499C-94BA-60FA3FD4739E@decibel.org (view raw or flat)
Thread:
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:
>  BEGIN;
>   CREATE INDEX idx_a ON t(a) DEFERRED;
>   CREATE INDEX idx_b ON t(b) DEFERRED;
>  COMMIT;
>
> 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! www.distributed.net Team #1828



In response to

Responses

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-2014 The PostgreSQL Global Development Group