DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=ltHtHHH6fG2IAvbZXp1zDhKqYhDtvwywtZKoBGrfpRpwtgWZxGMaLjKOv44RtSO1XKxtf2MFCDwDo0CyX8ZA1v87zutWC1bl+S60QJhPu77Xuup9kZnkYk7zlb0307RxQSLB+DFlIPuT10MeLSN8G/lLieWtK2+dkEBITX7rOZg=
	;
Message-ID: <20061129194339.47957.qmail@web82105.mail.mud.yahoo.com>
Date: Wed, 29 Nov 2006 11:43:39 -0800 (PST)
From: Mark Jensen <musicnyman1974@yahoo.com>
Subject: Fw: [GENERAL] Including unique users in huge data warehouse in
	Postgresql...
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=ascii
Content-Transfer-Encoding: quoted-printable

posting this here instead of the GENERAL list...richard is right, this is m=
ore of a performance question than a general question.=0A=0Athanks,=0A =0A_=
___________________________________=0AMark Jensen=0A=0A----- Forwarded Mess=
age ----=0AFrom: Mark Jensen <musicnyman1974@yahoo.com>=0ATo: Richard Huxto=
n <dev@archonet.com>=0ACc: pgsql-general@postgresql.org=0ASent: Wednesday, =
November 29, 2006 2:40:58 PM=0ASubject: Re: [GENERAL] Including unique user=
s in huge data warehouse in Postgresql...=0A=0Athanks Richard.  I've talkin=
g to Ron Mayer about this as well offline.  I think the main problem is ded=
upping users, and not being able to aggregate visits in the fact table.  th=
at's where most of the query time takes place.  but the business guys just =
won't accept using visits, but not actual uniques dedupped.  if visits was =
in the fact table as an integer i could sum up, i'd be fine.  Ron also said=
 he's put the unique user ids into arrays so it's faster to count them, but=
 placing them into aggregate tables.  only problem is i'd still have to kno=
w what things to aggregate by to create these, which is impossible since we=
 have so many dimensions and facts that are going to be ad-hoc.  i have abo=
ut 20 summary tables i create per day, but most of the time, they have some=
thing new they want to query that's not in summary.  and will never come up=
 again.=0A=0AI tried installing Bizgres using their Bizgres loader and cust=
om postgresql package with bitmap indexes, but doesn't seem to increase per=
formance "that" much.  or as much as i would like compared to the normal po=
stgresql install.  loads are pretty slow when using their bitmap indexes co=
mpared to just using btree indexes in the standard postgresql install.  Que=
ry time is pretty good, but i also have to make sure load times are accepta=
ble as well.  and had some problems with the bizgres loader losing connecti=
on to the database for no reason at all, but when using the normal copy com=
mand in 8.2RC1, works fine.  love the new query inclusion in the copy comma=
nd by the way, makes it so easy to aggregrate hourly fact tables into daily=
/weekly/monthly in one shot :)=0A=0Aand yes, work_mem is optimized as much =
as possible.  postgresql is using about 1.5 gigs of working memory when it =
runs these queries.  looking into getting 64 bit hardware with 16-32 gigs o=
f RAM so i can throw most of this into memory to speed it up.  we're also u=
sing 3par storage which is pretty fast.  we're going to try and put postgre=
sql on a local disk array using RAID 5 as well to see if it makes a differe=
nce.=0A=0Aand yes, right now, these are daily aggregate tables summed up fr=
om the hourly.  so about 17 million rows per day.  hourly fact tables are i=
mpossible to query right now, so i have to at least put these into daily fa=
ct tables.  so when you have 30 days in this database, then yes, table scan=
s are going to be huge, thus why it's taking so long, plus dedupping on uni=
que user id :)=0A=0Aand you're right, i should put this on the performance =
mailing list... see you there :)=0A=0Athanks guys.=0A =0A__________________=
__________________=0AMark Jensen=0A=0A----- Original Message ----=0AFrom: R=
ichard Huxton <dev@archonet.com>=0ATo: Mark Jensen <musicnyman1974@yahoo.co=
m>=0ACc: pgsql-general@postgresql.org=0ASent: Wednesday, November 29, 2006 =
2:29:35 PM=0ASubject: Re: [GENERAL] Including unique users in huge data war=
ehouse in Postgresql...=0A=0AMark Jensen wrote:=0A> So i've been given the =
task of designing a data warehouse in=0A> either Postgresql or Mysql for ou=
r clickstream data for our sites.  I=0A> started with Mysql but the joins i=
n Mysql are just way too slow=0A> compared to Postgresql when playing with =
star schemas.=0A=0AMark - it's not my usual area, but no-one else has picke=
d up your =0Aposting, so I'll poke my nose in. The other thing you might wa=
nt to do =0Ais post this on the performance list - that's probably the best=
 place. =0AMight be worth talking to those at www.bizgres.org too (although=
 I think =0Athey all hang out on the performance list).=0A=0A > I can't say=
=0A> which sites i'm working on, but we get close to 3-5 million uniques=0A=
> users per day, so over time, that's a lot of unique users to keep=0A> aro=
und and de-dup your fact tables by.  Need to be able to query normal=0A> an=
alytics like:=0A<snip>=0A=0A> i've=0A> made a lot of optimizations in postg=
resql.conf by playing with work_mem=0A> and shared_buffers and such and i t=
hink the database is using as much=0A> as it can disk/memory/cpu wise. =0A=
=0ABig work_mem, I'm guessing. Limiting factor is presumably disk I/O.=0A=
=0A<snip>=0A> here's a sample query that takes a while to run... just a sim=
ple report that shows gender by area of the site.=0A> =0A> select A.gender =
as gender, B.area as area, sum(C.imps) as imps, sum(C.clicks) as clicks, co=
unt(distinct(C.uu_id)) as users=0A> from uus as A, areas as B, daily_area_f=
act as C=0A> where A.uu_id =3D C.uu_id=0A> and B.area_id =3D C.area_id=0A> =
group by gender,area;=0A> =0A> so=0A> by just having one day of data, with =
3,168,049 rows in the user=0A> dimension table (uus), 17,213,420 in the dai=
ly_area_fact table that=0A> joins all the dimension tables, takes about 15 =
minutes.  if i had 30-90=0A> days in this fact table, who knows how long th=
is would take... i know=0A> doing a distinct on uu_id is very expensive, so=
 that's the main problem=0A> here i guess and would want to know if anyone =
else is doing it this way=0A> or better.=0A=0AIn the end, I'd suspect the s=
eq-scan over the fact table will be your =0Abiggest problem. Can you pre-ag=
gregate your fact-table into daily summaries?=0A=0ASee you over on the perf=
ormance list, where there are more experienced =0Apeople than myself to hel=
p you.=0A-- =0A   Richard Huxton=0A   Archonet Ltd=0A=0A=0A=0A=0A=0A =0A___=
___________________________________________________________________________=
______=0ADo you Yahoo!?=0AEveryone is raving about the all-new Yahoo! Mail =
beta.=0Ahttp://new.mail.yahoo.com=0A=0A---------------------------(end of b=
roadcast)---------------------------=0ATIP 5: don't forget to increase your=
 free space map settings=0A=0A=0A=0A=0A=0A =0A_____________________________=
_______________________________________________________=0AYahoo! Music Unli=
mited=0AAccess over 1 million songs.=0Ahttp://music.yahoo.com/unlimited