Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D6697632D9A for ; Wed, 10 Feb 2010 19:16:27 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20962-06 for ; Wed, 10 Feb 2010 23:16:17 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f209.google.com (mail-ew0-f209.google.com [209.85.219.209]) by mail.postgresql.org (Postfix) with ESMTP id 0067C632693 for ; Wed, 10 Feb 2010 19:16:16 -0400 (AST) Received: by ewy1 with SMTP id 1so24795ewy.34 for ; Wed, 10 Feb 2010 15:16:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=ml7+k/K7+XIeVPYpCL/ULCAShJh0SD9OjRN411za9N8=; b=OzbzPpHM447MK2KVbXE3pNfdwelNIRVzmIIwl8hKauZocud6ejMOncTyeulfeNgSLC JF8/ei09vjiwk/cJAvaMwFrTmXRlXRkkMH7KhSvaJTMseoI9N9N0ZJ5U/CPTRVj1jRZq Vh4KKr+F9LJ1lwmy+H3S16/IgPDn6OgSirLB8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=uL67A9tkGU2LONU/dT+AeRHMCFrnpfmhk+U+XnzjdlLjEDMcYCmc3nusXiXLIR700u fd3TqnEGN0MIE5HtW/FC0uyZXdIaU3XTLagvZxreHvdBWhdDzb0B76I0TI6XAeNdBDFr HREWJmuBP9Sf277FwEWH1A0Yce+2BYuwCfiXw= MIME-Version: 1.0 Received: by 10.216.88.78 with SMTP id z56mr540636wee.37.1265843775039; Wed, 10 Feb 2010 15:16:15 -0800 (PST) In-Reply-To: References: Date: Wed, 10 Feb 2010 17:16:14 -0600 Message-ID: Subject: Re: perf problem with huge table From: Dave Crooke To: rama Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d9a23b8d6952047f4738c8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.283 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 201002/167 X-Sequence-Number: 37588 --0016e6d9a23b8d6952047f4738c8 Content-Type: text/plain; charset=ISO-8859-1 Hi Rama I'm actually looking at going in the other direction .... I have an app using PG where we have a single table where we just added a lot of data, and I'm ending up with many millions of rows, and I'm finding that the single table schema simply doesn't scale. In PG, the table partitioning is only handled by the database for reads, for insert/update you need to do quite a lot of DIY (setting up triggers, etc.) so I am planning to just use named tables and generate the necessary DDL / DML in vanilla SQL the same way that your older code does. My experience is mostly with Oracle, which is not MVCC, so I've had to relearn some stuff: - Oracle often answers simple queries (e.g. counts and max / min) using only the index, which is of course pre-sorted. PG has to go out and fetch the rows to see if they are still in scope, and if they are stored all over the place on disk it means an 8K random page fetch for each row. This means that adding an index to PG is not nearly the silver bullet that it can be with some non-MVCC databases. - PG's indexes seem to be quite a bit larger than Oracle's, but that's gut feel, I haven't been doing true comparisons ... however, for my app I have limited myself to only two indexes on that table, and each index is larger (in disk space) than the table itself ... I have 60GB of data and 140GB of indexes :-) - There is a lot of row turnover in my big table (I age out data) .... a big delete (millions of rows) in PG seems a bit more expensive to process than in Oracle, however PG is not nearly as sensitive to transaction sizes as Oracle is, so you can cheerfully throw out one big "DELETE from FOO where ..." and let the database chew on it I am interested to hear about your progress. Cheers Dave On Wed, Feb 10, 2010 at 4:13 PM, rama wrote: > > > Hi all, > > i am trying to move my app from M$sql to PGsql, but i need a bit of help :) > > > on M$sql, i had certain tables that was made as follow (sorry pseudo code) > > contab_y > date > amt > uid > > > contab_yd > date > amt > uid > > contab_ymd > date > amt > uid > > > and so on.. > > this was used to "solidify" (aggregate..btw sorry for my terrible english) > the data on it.. > > so basically, i get > > contab_y > date = 2010 > amt = 100 > uid = 1 > > contab_ym > date = 2010-01 > amt = 10 > uid = 1 > ---- > date = 2010-02 > amt = 90 > uid = 1 > > > contab_ymd > date=2010-01-01 > amt = 1 > uid = 1 > ---- > blabla > > > in that way, when i need to do a query for a long ranges (ie: 1 year) i > just take the rows that are contained to contab_y > if i need to got a query for a couple of days, i can go on ymd, if i need > to get some data for the other timeframe, i can do some cool intersection > between > the different table using some huge (but fast) queries. > > > Now, the matter is that this design is hard to mantain, and the tables are > difficult to check > > what i have try is to go for a "normal" approach, using just a table that > contains all the data, and some proper indexing. > The issue is that this table can contains easilly 100M rows :) > that's why the other guys do all this work to speed-up queryes splitting > data on different table and precalculating the sums. > > > I am here to ask for an advice to PGsql experts: > what do you think i can do to better manage this situation? > there are some other cases where i can take a look at? maybe some > documentation, or some technique that i don't know? > any advice is really appreciated! > > > > > > > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --0016e6d9a23b8d6952047f4738c8 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Rama

I'm actually looking at going in the other direction ...= .

I have an app using PG where we have a single table where we just= added a lot of data, and I'm ending up with many millions of rows, and= I'm finding that the single table schema simply doesn't scale.
In PG, the table partitioning is only handled by the database for reads= , for insert/update you need to do quite a lot of DIY (setting up triggers,= etc.) so I am planning to just use named tables and generate the necessary= DDL / DML in vanilla SQL the same way that your older code does.

My experience is mostly with Oracle, which is not MVCC, so I've had= to relearn some stuff:

- Oracle often answers simple queries (e.g. = counts and max / min) using only the index, which is of course pre-sorted. = PG has to go out and fetch the rows to see if they are still in scope, and = if they are stored all over the place on disk it means an 8K random page fe= tch for each row. This means that adding an index to PG is not nearly the s= ilver bullet that it can be with some non-MVCC databases.

- PG's indexes seem to be quite a bit larger than Oracle's, but= that's gut feel, I haven't been doing true comparisons ...=A0 howe= ver, for my app I have limited myself to only two indexes on that table, an= d each index is larger (in disk space) than the table itself ... I have 60G= B of data and 140GB of indexes :-)

- There is a lot of row turnover in my big table (I age out data) .... = a big delete (millions of rows) in PG seems a bit more expensive to process= than in Oracle, however PG is not nearly as sensitive to transaction sizes= as Oracle is, so you can cheerfully throw out one big "DELETE from FO= O where ..." and let the database chew on it

I am interested to hear about your progress.

Cheers
Dave
<= br>
On Wed, Feb 10, 2010 at 4:13 PM, rama <rama.rama@tiscali.it= > wrote:


Hi all,

i am trying to move my app from M$sql to PGsql, but i need a bit of help :)=


on M$sql, i had certain tables that was made as follow (sorry pseudo code)<= br>
contab_y
=A0 date
=A0 amt
=A0 uid


contab_yd
=A0date
=A0amt
=A0uid

contab_ymd
=A0date
=A0amt
=A0uid


and so on..

this was used to "solidify" (aggregate..btw sorry for my terrible= english) the data on it..

so basically, i get

contab_y
date =3D 2010
amt =3D 100
uid =3D 1

contab_ym
=A0date =3D 2010-01
=A0amt =3D 10
=A0uid =3D 1
----
=A0date =3D 2010-02
=A0amt =3D 90
=A0uid =3D 1


contab_ymd
=A0 date=3D2010-01-01
=A0 amt =3D 1
=A0 uid =3D 1
----
blabla


in that way, when i need to do a query for a long ranges =A0(ie: 1 year) i = just take the rows that are contained to contab_y
if i need to got a query for a couple of days, i can go on ymd, if i need t= o get some data for the other timeframe, i can do some cool intersection be= tween
the different table using some huge (but fast) queries.


Now, the matter is that this design is hard to mantain, and the tables are = difficult to check

what i have try is to go for a "normal" approach, using just a ta= ble that contains all the data, and some proper indexing.
The issue is that this table can contains easilly 100M rows :)
that's why the other guys do all this work to speed-up queryes splittin= g data on different table and precalculating the sums.


I am here to ask for an advice to PGsql experts:
what do you think i can do to better manage this situation?
there are some other cases where i can take a look at? maybe some documenta= tion, or some technique that i don't know?
any advice is really appreciated!










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

--0016e6d9a23b8d6952047f4738c8--