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