Обсуждение: index speed-up and automatic tables/procedures creation
Hi,
I've got some questions:
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.)
2)- could somebody points me to an URL that describes (examples) automatic tables
& functions making?
Thanks in advance
JY
--
panic("Foooooooood fight!");
-- In the kernel source aha1542.c, after detecting a bad segment list
On Thu, Nov 26, 2009 at 10:19 PM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: > 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.) There are plenty of people with tables with many more than several million records. How big that is depends on how wide those rows are, but still, it's not necessarily a problem. Indexed access speed should scale fine. The real problem that partitioning addresses is routine maintenance. When it comes time to dump this table or create a new index or even just scan a large section of the table for a report you may find the jobs taking impracticably long. -- greg
Greg Stark a écrit :
> On Thu, Nov 26, 2009 at 10:19 PM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote:
>> 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.)
>
> There are plenty of people with tables with many more than several
> million records. How big that is depends on how wide those rows are,
> but still, it's not necessarily a problem. Indexed access speed should
> scale fine.
>
> The real problem that partitioning addresses is routine maintenance.
> When it comes time to dump this table or create a new index or even
> just scan a large section of the table for a report you may find the
> jobs taking impracticably long.
Sooo, I guess the answer is: cut your table by pieces=year?
--
<Overfiend> ltd: Fine, go through life just pointing and grunting at
what you mean. Works for Mac users.
On Fri, Nov 27, 2009 at 3:15 AM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: > Sooo, I guess the answer is: cut your table by pieces=year? > Well there's no one-size-fits-all solution. It'll depend on your priorities and your situation. -- greg
Greg Stark a écrit : > On Fri, Nov 27, 2009 at 3:15 AM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: >> Sooo, I guess the answer is: cut your table by pieces=year? >> > > Well there's no one-size-fits-all solution. It'll depend on your > priorities and your situation. > Ok, this one will be the items' table, as there can be many types of links between an item and different documents, links will be done by tables containing item(id),doc(id) - one for each type of doc. May be, I'm not sure at the moment, when items will be out of warranty and/or documents won't need to be legally kept online, I'll move them to itema (for Archive) - and the same for docs. -- Very few profundities can be expressed in less than 80 characters.
"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
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
Tom Lane a écrit : ... > 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. Just to make sure I understood the spirit: * I keep a large table, * As my join tables have just (pkey=pkeys from each side), I also make indexes on each foreign pkey, * (May be?) I also make partial indexes, in order to have ie a faster retrieve of not-sold items instead of excluding sold items in the query JY -- -- I have seen the FUN --