indexes, and tables within tables
От | Jaime Silvela |
---|---|
Тема | indexes, and tables within tables |
Дата | |
Msg-id | 461D14C2.5090506@bear.com обсуждение исходный текст |
Список | pgsql-general |
I was reading an interview with Chris Date the other day, which got me thinking about a problem I'm currently having: I have an application that keeps information in 6 denormalized tables, D1 through D6. To tie things together, all these tables have a common column, let's call it obj_id. There is an additional table P, that is generated from a query that uses D1 through D3 and JOINS them to another table, X. P also has this obj_id column. All these tables are pretty big, so I keep them indexed by obj_id. Table P in particular is a huge time series which I also need to keep indexed by date, so there is an index on P on (obj_id, date). Now, my problem: I need to keep P indexed, because I need to use it in queries very often, and I need them to be quick. However, due to the indexing, putting new rows in P takes forever, which it didn't used to when it wasn't indexed. I know that the typical solution is to drop the index, import the new data, and create the index again. However, this doesn't work for me, as the application is pretty interactive and users could be querying P and adding rows to it (on different obj_id's) simultaneously. The ideal situation would be if reindexing didn't reindex the whole table, but just those entries added with the given obj_id. This, as far as I know, is not possible. I don't really need table-wide indexes. The data in different obj_id's is not compared very often - what happens all the time is the cross-table relationships on the same obj_id. So what Date seems to wish for RDBMs to handle objects would work here: I would have a table of obj_id's, with columns D1 .. D6 and P. The object stored in a (row, column) would be a table with the appropriate entries, and in the case of P, an index on date. Retrieving all the related entries for a given obj_id would be trivial, as would inserting a new table object into the P column, for a given obj_id. Are there plans to handle table objects as possible values in other tables, as Date seems to propose? Is there currently a way of easing the design problem here? Any advice on redesigning the data? Thanks Jaime *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************
В списке pgsql-general по дате отправления:
Следующее
От: Martijn van OosterhoutДата:
Сообщение: Re: The rule question before, request official documentation on the problem