Re: Partition table in 9.0.x?

On Tue, Jan 8, 2013 at 10:04 AM, AJ Weber <aweber(at)comcast(dot)net> wrote:
>>
>> The current constraint exclusion code is quite simple-minded and
>> doesn't know how to make use of check constraints that use the mod
>> function, so the indexes of all partitions would have to be searched
>> for each order_num-driven query, even though we know the data could
>> only exist in one of them. The constraint exclusion codes does
>> understand check constraints that involve ranges.
>
> Hmm. That's a bit of a limitation I didn't know about. I assume it doesn't
> understand the percent (mod operator) just the same as not understanding the
> MOD() function? Either way, I guess this strategy does not pan-out.

Yes, it doesn't detect either. It would use it if you formulate to
every equality query with an extra restriction: "where id=1234567 and
mod(id,100)=67" or whatever.

(But I was surprised that % and mod() are not recognized as being
equivalent. If you specify it one way in the check constraint, you
need to use the same "spelling" in the where clause)

>> There could still be some benefit as the table data would be
>> concentrated, even if the index data is not.
>
> I'm reaching way, way back in my head, but I think _some_ RDBMS I worked
> with previously had a way to "cluster" the rows around a single one of the
> indexes on the table, thus putting the index and the row-data "together" and
> reducing the number of IO's to retrieve the row if that index was used.

In Oracle this is called in "index organized table" or IOT (or it was
at one point, they have the habit of rename most of their features
with each release). I don't know what other RDBMS call it.
Supporting secondary indexes when the table data could move around was
quite intricate/weird.

PG doesn't have this index-organized-table feature--it has been
discussed but I don't of any currently active effort to add it.

There is another feature, sometimes called clustering, in which the
rows from different tables can be mingled together in the same block.
So both the parent order and the child order_line_item that have the
same order_num (i.e. the join column) would be in the same block. So
once you query for a specific order and did the necessary IO, the
corresponding order_line_item rows would already be in memory. I
thought this was interesting, but I don't know how often it was
actually used.

> Am
> I understanding that PG's "cluster" is strictly to group like rows together
> logically -- table data only, not to coordinate the table row with the index
> upon which you clustered them?

They are coordinated in a sense. Not as one single structure, but as
two structures in parallel.

>>> and all rows for the same order would stay
>>> within the same partition-table.
>>
>> But usually a given order_num would only be of interest for a fraction
>> of a second before moving on to some other order_num of interest, so
>> by the time the relevant partition become fully cached, it would no
>> longer be hot. Or, if the partitions were small enough, you could
>> assume that all rows would be dragged into memory when the first one
>> was requested because they lay so close to each other. But it is not
>> feasible to have a large enough number of partitions to make that
>> happen. But if the table is clustered, this is exactly what you would
>> get--the trouble would be keeping it clustered. If most of the
>> line-items are inserted at the same time as each other, they probably
>> should be fairly well clustered to start with.
>
> Does decreasing the fill to like 90 help keep it clustered in-between times
> that I could shutdown the app and perform a (re-) cluster on the overall
> table? Problem is, with a table that size, and the hardware I'm "blessed
> with", the cluster takes quite a bit of time. :(

Probably not. If the data starts out clustered and gets updated a
lot, lowering the fill factor might be able to prevent some
de-clustering due to row migration. But when you insert new rows, PG
makes no effort to put them near existing rows with the same key. (In
a hypothetical future in which that did happen, lowering the fill
factor would then probably help)

Cheers,

Jeff