DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=pqoJGe4cK3EEj565BI1tYXOhcnTol3a7FjTpQGNrwHm5lnG01BxQHbAW4c5zr4F3pRv5eB3DgQ7q1V7ZDe3nZ2693vp1r01Zdgw+AFnLoPehXzWNbau8PQBOxmiBlb+rQXm5j7uEt8TA2LqBVuiiRblV+arMQowrQ1CsqvyCbxs=
Message-ID: <b42b73150705041010nf91213ci7ef41ee07e4c79bf@mail.gmail.com>
Date: Fri, 4 May 2007 13:10:16 -0400
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Scott Marlowe" <smarlowe@g2switchworks.com>
Subject: Re: Query performance problems with partitioned tables
Cc: "Fei Liu" <fei.liu@aepnetworks.com>, "Andreas Haumer" <andreas@xss.co.at>,
	pgsql-performance@postgresql.org
In-Reply-To: <1178291472.2953.47.camel@state.g2switchworks.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <4635D678.3050205@xss.co.at> <4639F97D.3080003@aepnetworks.com>
	<b42b73150705031937g7884ae55ha9cc670e26a27340@mail.gmail.com>
	<1178291472.2953.47.camel@state.g2switchworks.com>

On 5/4/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> On Thu, 2007-05-03 at 21:37, Merlin Moncure wrote:
> > On 5/3/07, Fei Liu <fei.liu@aepnetworks.com> wrote:
> > > Hello, Andreas, I too am having exactly the same issue as you do.
> > > Comparing my partitioned and plain table performance, I've found that
> > > the plain tables perform about 25% faster than partitioned table. Using
> > > 'explain select ...', I see that constraints are being used so in
> > > partitioned tables fewer rows are examined. But still partitioned tables
> > > are 25% slower, what a let down.
> >
> > That's a little bit harsh.  The main use of partitioning is not to
> > make the table faster but to make the maintenance easier.  When
> > constraint exclusion works well for a particular query you can get a
> > small boost but many queries will break down in a really negative way.
> >  So, you are sacrificing flexibility for easier maintenance.  You have
> > to really be careful how you use it.
> >
> > The best case for partitioning is when you can logically divide up
> > your data so that you really only have to deal with one sliver of it
> > at a time...for joins and such.  If the OP could force the constraint
> > exclusion (maybe by hashing the timestamp down to a period and using
> > that for where clause), his query would be fine.  The problem is it's
> > not always easy to do that.
>
> Agree++
>
> I've been testing partitioning for a zip code lookup thing that was
> posted here earlier, and I partitioned a 10,000,000 row set into about
> 400 partitions.  I found that selecting a range of areas defined by x/y
> coordinates was faster without any indexes.  The same selection with one
> big table and one big (x,y) index took 3 to 10 seconds typically, same
> select against the partitions with no indexes took 0.2 to 0.5 seconds.

I was thinking about that problem....one approach I was playing with
was to normalize the 10mm table to zipcode (chopping off + 4) and then
doing bounding box ops on the zipcode (using earthdistance/gist) table
and also the detail table using tradictional tactics or gist.  I think
this would give reasonable performance without partitioning (10mm
records doesn't scare me anymore!).  If the records are frequently
updated you may want to TP anways though do to (pre-hot) vacuum
issues.

merlin