Re: index speed-up and automatic tables/procedures creation

Tom Lane a écrit :
> "Jean-Yves F. Barbier" <12ukwn(at)gmail(dot)com> writes:
>> 1)- I'd like to keep a table in one piece, but it'll be huge (several millions rows
>> and growing); can a segmentation of indexes (all indexes that are used for
>> searching) speed-up this table scans enough to keep it as responsive to queries as
>> multiple tables? And what can I do about the primary key index, which is monolitic?
>> (I can't use inheritance as there are some integrity references into it.)
>
> I think you're wasting your time. What you are setting out to do here
> is manually emulate the top layer or so of a large index. Unless you
> have very specific (and unusual) data access patterns that you know in
> considerable detail, this is not a game you are going to win. Just go
> with the one big table and one index, you'll be happier. (Note that
> "several million rows" is not big, it's barely enough to notice.)
>
> You will see a lot of discussion about partitioning of tables if you
> look around the list archives, but this is not done with the idea that
> it makes access to any one row faster. The biggest motivation usually
> is to allow dropping ranges of data cheaply, like throwing away a month's
> or year's worth of old data at once.
>
> regards, tom lane

That's a *very* clear answer, thanks Tom!

JY
--
If a thing's worth having, it's worth cheating for.
-- W. C. Fields