Re: Rules and WITH and LATERAL

On Sun, Aug 19, 2012 at 12:06:30PM -0400, Tom Lane wrote:
> While thinking about this I wondered whether it might be possible to
> clean up the implementation of rules, and perhaps also get rid of some
> of their semantic issues, by making the rule rewriter rely on WITH
> and/or LATERAL, neither of which we had back in the dark ages when the
> current rules implementation was built. In particular, WITH might offer
> a fix for the multiple-evaluation gotchas that people so often trip
> over. For instance, perhaps an UPDATE with rules could be rewritten
> into something like

Making the rule system use WITH always seemed like a good idea to me.
ISTM though that it would tax the optimiser, as it would need to become
much more clever at pushing conditions down. For example, on 9.1 at
least you still get this:

$ explain with x as (select * from pg_class) select * from x where relname = 'test';
QUERY PLAN
---------------------------------------------------------------------
CTE Scan on x (cost=14.15..23.49 rows=2 width=189)
Filter: (relname = 'test'::name)
CTE x
-> Seq Scan on pg_class (cost=0.00..14.15 rows=415 width=194)
(4 rows)

whereas without the with you get an index scan.

So in its current form you can't use WITH to simplify the
implementation of views because performence would suck. OTOH, the
intelligence in the current rule system may be a good guide to optimise
WITH statements.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer