Re: Planner features, discussion

On 15/07/10 00:34, David Fetter wrote:

>> => WITH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval FROM
>> generate_series(1,10) AS x;
>> ERROR: column "constval" does not exist
>> LINE 1: ...TH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval F...
>
> You missed the CROSS JOIN, which you could make implicit, even though
> implicit CROSS JOINs are bad coding style:

It was an example of how it'd be nice to avoid the need for a join when
dealing with scalar values. I'd love to be able to write:

WITH aconstant AS (1)
SELECT x.*, aconstant FROM generate_series(1,10) AS x;

... but can't presently do so because the WITH terms are only visible as
potential from-list items.

> WITH aconstant(constval) AS (VALUES(1))
> SELECT x.*, constval
> FROM
> generate_series(1,10) AS x
> CROSS JOIN
> aconstant;
> x | constval
> ----+----------
> 1 | 1
> 2 | 1
> 3 | 1
> 4 | 1
> 5 | 1
> 6 | 1
> 7 | 1
> 8 | 1
> 9 | 1
> 10 | 1
> (10 rows)

Using a cross join can often result in an undersired and expensive
nested loop, (I think) materialize, etc. In this case, the planner is
using a nested loop to join `aconstant' with the output of the function
scan:

> Nested Loop (cost=0.01..22.53 rows=1000 width=8) (actual time=0.049..0.133 rows=10 loops=1)
> CTE aconstant
> -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)
> -> CTE Scan on aconstant (cost=0.00..0.02 rows=1 width=4) (actual time=0.015..0.023 rows=1 loops=1)
> -> Function Scan on generate_series x (cost=0.00..12.50 rows=1000 width=4) (actual time=0.022..0.045 rows=10 loops=1)
> Total runtime: 0.223 ms

as compared to what happens when I explicitly insert the constant by
hand or wrap the query up in an SQL function that takes the constant as
a parameter:

> Function Scan on generate_series x (cost=0.00..12.50 rows=1000 width=4) (actual time=0.027..0.054 rows=10 loops=1)
> Total runtime: 0.125 ms

In this trivial dummy example, it doesn't matter much. But in the kinds
of complex queries you often want to use a WITH expression for, it's not
appealing. If you're trying to use a WITH expression to avoid multiple
evaluation of an expensive function, the gains are often consumed in the
join costs.

So I land up relying on wrapping things up in SQL functions instead,
which is less than thrilling.

>> ... so you're forced to fall back on adding it as an additional join
>> expression - which isn't always reasonable or possible.
>
> Why not?

As above for one reason.

--
Craig Ringer