From: Sam Vilain <sam@vilain.net>
To: molson@oceanconsulting.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Prefetch
Date: Wed, 11 May 2005 16:33:50 +1200
Message-ID: <42818B2E.8060403@vilain.net> (raw)
In-Reply-To: <200505100652.51401.molson@oceanconsulting.com>
References: <200505100652.51401.molson@oceanconsulting.com>
Matt Olson wrote:
> Other databases like Oracle and DB2 implement some sort of row prefetch. Has
> there been serious consideration of implementing something like a prefetch
> subsystem? Does anyone have any opinions as to why this would be a bad idea
> for postgres?
> Postges is great for a multiuser environment and OLTP applications. However,
> in this set up, a data warehouse, the observed performance is not what I
> would hope for.
Oracle doesn't pre-fetch data to get its fast results in this case.
pre-fetching doesn't give you the 100 times speed increases.
Bitmap indexes are very important for data mining. You might want to see
http://www.it.iitb.ac.in/~rvijay/dbms/proj/
I have no idea how well developed this is, but this is often the biggest
win with Data Warehousing. If it works, you'll get results back in seconds,
if it doesn't you'll have plenty of time while your queries run to reflect on
the possibility that commercial databases might actually have important features
that haven't even penetrated the awareness of most free database developers.
Another trick you can use with large data sets like this when you want results
back in seconds is to have regularly updated tables that aggregate the data
along each column normally aggregated against the main data set.
Of couse, Pg doesn't have the nice features that make this just work and make
queries against your data source faster (called "OLAP Query rewrite" in
Oracle), so you'll have to put a lot of work into changing your application to
figure out when to use the summary tables. As far as I know it doesn't have
materialized views, either, so updating these summary tables is also a more
complex task than just a single REFRESH command.
Maybe some bright person will prove me wrong by posting some working
information about how to get these apparently absent features working.
You might also want to consider ditching RAID 5 and switching to plain
mirroring. RAID 5 is a helluva performance penalty (by design). This is
why they said RAID - fast, cheap, reliable - pick any two. RAID 5 ain't
fast. But that's probably not your main problem.
Sam.
>
> Regards,
>
> Matt Olson
> Ocean Consulting
> http://www.oceanconsulting.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sam@vilain.net, molson@oceanconsulting.com
Subject: Re: Prefetch
In-Reply-To: <42818B2E.8060403@vilain.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox