Skip site navigation (1) Skip section navigation (2)

Re: partitioning question 1

From: Ben <midfield(at)gmail(dot)com>
To: Igor Neyman <ineyman(at)perceptron(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: partitioning question 1
Date: 2010-10-29 16:16:13
Message-ID: 59555E0F-71C2-4062-AD23-F1B7B9993354@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote:

>> is my intuition completely off on this?
>> 
>> best regards, ben
>> 
> 
> If your SELECT retrieves substantial amount of records, table scan could
> be more efficient than index access.
> 
> Now, if while retrieving large amount of records "WHERE clause" of this
> SELECT still satisfies constraints on some partition(s), then obviously
> one (or few) partition scans will be more efficient than full table scan
> of non-partitioned table.
> 
> So, yes partitioning provides performance improvements, not only
> maintenance convenience.

my impression was that a *clustered* index would give a lot of the same I/O benefits, in a more flexible way.  if you're clustered on the column in question, then an index scan for a range is much like a sequential scan over a partition (as far as i understand.)

b

In response to

Responses

pgsql-performance by date

Next:From: Igor NeymanDate: 2010-10-29 16:28:20
Subject: Re: partitioning question 1
Previous:From: Tom LaneDate: 2010-10-29 15:57:06
Subject: Re: BBU Cache vs. spindles

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group