问题描述
在北卡罗来纳州立大学，我的学生和我正在开展一个名为
自组织数据库的项目。请看下面的说明。我想
使用一个开源数据库系统来实现，并且真的会感谢你对PostgreSQL是否适合该项目的看法。
一般来说，PostgreSQL代码的质量和
文档以及开发人员社区的支持给我留下了非常深刻的印象。
For该项目，我需要一个基于成本的查询优化器，详尽的连接
枚举和使用存储关系的统计数据; PostgreSQL有这个。
我还需要能够使用聚合处理SQL查询，广泛的b
索引功能，查看机制以及可能的完整性约束;
似乎PostgreSQL拥有这一切。我们将修改查询优化器为
使用视图合并重写查询，我们将创建
视图生成和视图操作模块。
如果您有任何意见，请告诉我。
此致，
Rada Chirkova
======================
自组织数据库
该项目的目标是开发新的有效方法，以便始终在大型关系数据库b b数据库中提高常见和重要查询集的性能，从而提高效率用户
与数据管理系统的交互。解决问题将对查询优化，数据仓库和信息产生最大的影响
集成，这是直接实用的重要研究课题
应用程序。
该项目侧重于使用视图评估查询的方法;
视图是由辅助查询定义的关系，可用于
重写并回答用户查询。提高查询性能的一种方法是预先计算和存储（即物化）视图。
要真正优化查询性能，实现
" right"观点。该项目目前的重点是通过
设计和实现视图来证明，有可能确保频繁和重要查询的最佳或近乎优化的性能，对于常见和
重要的查询类型。我们在设计自组织数据库的更广泛背景下考虑这个问题：定期自组织数据库
在没有人为干预的情况下确定一组代表性的频繁和
对数据进行重要查询，并逐步设计和预先计算该代表性查询工作负载的最佳（或接近最佳）视图。随着代表查询工作量和存储的数据随时间的变化，
自组织数据库通过更改
的设置来适应更改物化视图，用于提高数据库中的查询 - 回答性能
。
为了构建自组织数据库，我们考虑结束 - 最终解决方案？
即我们考虑处理和使用视图的所有方面，包括：
·设计和实现视图和索引以改进查询
性能;
·探索物化视图对查询过程的影响
优化;
·使视图设计适应不断变化的查询工作量，包括流程
不再有用的退休视图;
·开发自动更新现有物化视图的方法
随着时间的推移，反映存储数据的变化;
·开发方法t o收集数据库统计数据，以可靠地估计系统考虑实现的视图的大小;
·分析系统资源的使用并分配适当的数量
用于查看系统管理的资源。
---------------------------（结束广播）---------------------------
提示3：如果通过Usenet发布/阅读，请发送适当的
subscribe-nomail命令这样你的
信息就可以干净利落地进入邮件列表
Hi,
at NC State University, my students and I are working on a project called
"self-organizing databases," please see description below. I would like to
use an open-source database system for implementation and would really
appreciate your opinion on whether PostgreSQL is suitable for the project.
In general, I am very impressed by the quality of PostgreSQL code and
documentation, as well as by the support of the developer community.
For the project, I need a cost-based query optimizer with exhaustive join
enumeration and use of statistics on stored relations; PostgreSQL has that.
I also need the ability to process SQL queries with aggregation, extensive
indexing capabilities, view mechanisms, and possibly integrity constraints;
it seems that PostgreSQL has all that. We will modify the query optimizer to
incorporate rewriting queries using views, and we will create
view-generating and view-manipulating modules.
Please let me know if you have comments.
Sincerely,
Rada Chirkova
======================
Self-Organizing Databases
The goal of this project is to develop new effective methods to improve the
performance of sets of frequent and important queries on large relational
databases at all times, which could improve the efficiency of user
interactions with data-management systems. Solving the problem will have the
most effect in query optimization, data warehousing, and information
integration, which are important research topics with direct practical
applications.
The project focuses on the methodology of evaluating queries using views;
views are relations that are defined by auxiliary queries and can be used to
rewrite and answer user queries. One way to improve query performance is
precompute and store (i.e., materialize) views.
To truly optimize query performance, it is critical to materialize the
"right" views. The current focus of the project is on demonstrating that, by
designing and materializing views, it is possible to ensure optimal or
near-optimal performance of frequent and important queries, for common and
important query types. We consider this problem in the broader context of
designing self-organizing databases: A self-organizing database periodically
determines, without human intervention, a representative set of frequent and
important queries on the data, and incrementally designs and precomputes the
optimal (or near-optimal) views for that representative query workload. As
the representative query workload and the stored data change over time,
self-organizing databases adapt to the changes by changing the set of
materialized views that are used to improve the query-answering performance
in the database.
For building self-organizing databases, we consider an end-to-end solution ?
that is, we consider all aspects of handling and using views, including:
·designing and materializing views and indexes to improve query
performance;
·exploring the effects of materialized views on the process of query
optimization;
·adapting view design to the changing query workload, including the process
of retiring views that are no longer useful;
·developing methods for auomatically updating existing materialized views
over time, to reflect the changes in the stored data;
·developing methods to collect database statistics to reliably estimate the
sizes of the views the system considers for materialization;
·analyzing the use of system resources and allocating an appropriate amount
of resources to view management in the system.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
推荐答案
FWIW，我认为Postgres会很好地作为你项目的基础。
但有些人可能会说我有偏见;-)
Postgres最初是作为一种研究工具而设计和构建的，它仍然具有明显的遗产痕迹，尽管现在的开发人员几乎专心致力于转向它成为一个
生产工具。您可能会觉得这很有趣：
我个人并不相信物化观点会很棒>
添加到Postgres的东西，但我期待被证明是错误的。
请告诉我们你的结果。
问候， tom lane
PS：顺便说一句，pgsql-hackers可能比pgsql-general更好，因为讨论与Postgres的研究工作。
---------------------------（广播结束）----------- ----------------
提示9：如果您的
加入专栏，计划员将忽略您选择索引扫描的愿望'的数据类型不匹配
FWIW, I think Postgres will do very well as a base for your project.
But some might say I''m biased ;-)
Postgres was originally designed and built as a research tool, and it
still bears obvious traces of that heritage, even though the current
developers are almost single-mindedly focused on turning it into a
production tool. You might find this entertaining:
http://archives.postgresql.org/pgsql...6/msg00085.php
I''m not personally convinced that materialized views would be a great
thing to add to Postgres, but I look forward to being proven wrong.
Please keep us posted on your results.
regards, tom lane
PS: BTW, pgsql-hackers is probably a better list than pgsql-general for
discussing research work with Postgres.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
我还使用视图来简化应用程序代码，将
复杂查询移到后端。我想知道什么，如果有的话，
将这个使用视图的方面放在你的项目中。
谢谢，
Karsten
-
GPG密钥ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---------------------------（广播结束）----------- ----------------
提示1：订阅和取消订阅命令转到
I also use views to simplify application code by moving
complicated queries onto the backend. I wonder what, if any,
place this aspect of using views has in your project.
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
你指的是Access'倾向于在客户端进行连接吗？
-
--------------------------------------- --------------------------
Ron Johnson，Jr。
美国路易斯安那州杰斐逊
你有什么其他证据证明他们是恐怖分子，其他人比他们在这些（恐怖主义训练）训练营训练的人还要多吗？
17-Sep-2002 Katie Couric向一名联邦调查局特工询问了5名男子
在布法罗纽约附近被捕
------------- --------------（广播结束）---------------------------
提示6：您是否搜索了我们的列表档案？
Are you referring to Access'' tendency to do the joins on the client?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA
"What other evidence do you have that they are terrorists, other
than that they trained in these (terrorist training) camps?"
17-Sep-2002 Katie Couric to an FBI agent regarding the 5 men
arrested near Buffalo NY
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
这篇关于我可以使用PostgreSQL开发自组织数据库系统吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！