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

Tom Lane a écrit :
> "Jean-Yves F. Barbier" <12ukwn@gmail.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