Re: [HACKERS] Re: [SQL] cursor and update + view
От | jwieck@debis.com (Jan Wieck) |
---|---|
Тема | Re: [HACKERS] Re: [SQL] cursor and update + view |
Дата | |
Msg-id | m0ziKjE-000EBYC@orion.SAPserv.Hamburg.dsh.de обсуждение исходный текст |
Ответ на | Re: [HACKERS] Re: [SQL] cursor and update + view (Vadim Mikheev <vadim@krs.ru>) |
Список | pgsql-hackers |
Vadim wrote: > This thing would also handled by subqueries in FROM! > Having support in planner/executor for queries like this: > > select * from A, (select c, max(d) as m from B group by c) SQ > where SQ.c = A.x and SQ.m = A.y > > rule system will be able to put _any_ VIEW' query into > FROM clause... Possible - but IMHO the wrong thing to do. As it is now for a view that has no aggregate, the rule system rewrites the query to something that is the same as if the user resolved the view definition by hand and used all the real tables instead. Have the following: CREATE TABLE t1 (a int4, b int4); CREATE TABLE t2 (a int4, c int4); CREATE TABLE t3 (a int4); CREATE VIEW v1 AS SELECT t1.a, t1.b, t2.c FROM t1, t2 WHERE t1.a = t2.a; Now do a SELECT t3.a, v1.b, v1.c FROM t3, v1 WHERE t3.a = v1.a; The current rewrite system builds a querytree that is exactly that what would have been produced by the parser if you had typed SELECT t3.a, t1.b, t2.c FROM t3, t1, t2 WHERE t3.a = t1.a AND t1.a = t2.a; Now the planner/optimizer has _ALL_ the tables that need to be scanned and _ALL_ the qualifications in _ONE_ querytree. It is the job of the optimizer to decide which is the best join path for this access. To make a good decision, it needs all this information plus the VACUUM statistics. If we put any view into a subquery RTE, we force the planner to materialize the view and do a nestloop over t3 and materialized v1 where possibly using t1 or t2 as the outermost scanrelation would be better. Stonebraker & Co where absolutely right when they spoke about productional rule systems. And what PostgreSQL does now is how I understood them. "Production rule systems are conceptually simple, but there are many subtle points involved in actually using them." -- Stonebraker I think the different grouping requirements for subsets of data when using aggregate columns in views is just one of the the problems he addressed with the above statement. We should build a subquery RTE only if Query->hasAggs is true. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
В списке pgsql-hackers по дате отправления: