Re: Compression and on-disk sorting

On Wed, May 17, 2006 at 12:16:13PM -0400, Rod Taylor wrote:
> > Actually, I suspect in most cases it won't matter; I don't think people
> > make a habit of trying to sort their entire database. :) But we'd want
> > to protect for the oddball cases... yech.
>
> I can make query result sets that are far larger than the database
> itself.
>
> create table fat_table_with_few_tuples(fat_status_id serial primary key,
> fat_1 text, fat_2 text);
>
> create table thin_table_with_many_tuples(fat_status_id integer
> references fat_table_with_few_tuples, thin_1 integer, thin_2 integer);
>
> SELECT * FROM thin_table_with_many_tuples NATURAL JOIN
> fat_table_with_few_tuples order by fat_1, thin_1, thin_2, fat_2;
>
>
> I would be asking the folks trying to use PostgreSQL for data
> warehousing what their opinion is. A few fact tables in an audit query
> could easily result in a very large amount of temporary diskspace being
> required.

Note my last sentence: we'd need to provide for cases where this was a
problem. How much that would complicate the code, I don't know...

This is another case where someone (with more skills than me) would need
to hack the backend enough to be able to test it and see how big a
performance gain there was.
--
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