Prev: CommitFest 2010-07 week one progress report
Next: non-overlapping, consecutive partitions
From: =?iso-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= on 23 Jul 2010 16:04
hello everybody,

i have just come across some issue which has been bugging me for a while.
consider:

SELECT * FROM foo ORDER BY bar;

if we have an index on bar, we can nicely optimize away the sort step by consulting the index - a btree will return sorted output.
under normal circumstances it will be seq->sort but doing some config settings we can turn this into an index scan nicely to avoid to the sort (disk space is my issue here).

this is not so easy anymore:

create table foo ( x date );
create table foo_2010 () INHERITS (foo)
create table foo_2009 () INHERITS (foo)
create table foo_2008 () INHERITS (foo)

now we add constraints to make sure that data is only in 2008, 2009 and 2010.
we assume that everything is indexed:

SELECT * FROM foo ORDER BY bar will now demand an ugly sort for this data.
this is not an option if you need more than a handful of rows ...

if constraints are non overlapping and if they are based on a "sortable" data type, we might be able to scan one index after the other and get a sorted list.
why is this an issue? imagine a case where you want to do billing, eg. some phone calls. the job now is: the last 10 calls of a customer are free and you want to sum up those which are not free.
to do that you basically need a sorted list per customer. if you have data here which is partitioned over time you are screwed up because you want to return a sorted list taken from X partitions to some higher level operation (windowing or whatever).
resorting vast amounts of data is a killer here. in the particular case i am talking about my problem is roughly 2 TB scaled out to some PL/proxy farm.

does anybody see a solution to this problem?
what are the main showstoppers to make something like this work?

many thanks,

hans

--
Cybertec Sch�nig & Sch�nig GmbH
Gr�hrm�hlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 | 
Pages: 1
Prev: CommitFest 2010-07 week one progress report
Next: non-overlapping, consecutive partitions