How can I 'fake' stats for generate_series data?

Question

With 9.1 and earlier, using generate_series in the select clause does not increase the expected row count (but of course does increase the actual row count), leading to possible poor planning:

select split_part(version(),' ',2);
/*
 split_part
------------
 9.1.4
*/
explain analyze 
    select foo, generate_series(1,10) 
    from (select 'A' as foo) z
;
/*
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Subquery Scan on z  (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.014 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
*/

note: (cost=0.00..0.02rows=1width=32) (actual time=0.010..0.014rows=10loops=1)

This is improved in 9.2, giving the standard 1000 estimate:

select split_part(version(),' ',2);
/*
 split_part
------------
 9.2.1
*/
explain analyze 
    select foo, generate_series(1,10) 
    from (select 'A' as foo) z
;
/*
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Subquery Scan on z  (cost=0.00..5.02 rows=1000 width=32) (actual time=0.014..0.020 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
*/

note: (cost=0.00..5.02rows=1000width=32) (actual time=0.014..0.020rows=10loops=1)

How can I 'fake' this behaviour in 9.1 and earlier?

Answer 1

Perform a dummy cross join on a single row generate_series:

select split_part(version(),' ',2);
/*
 split_part
------------
 9.1.4
*/
explain analyze 
    select foo, generate_series(1,10) 
    from (select 'A' as foo) z 
    cross join generate_series(1,1)
;
/*
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..22.52 rows=1000 width=32) (actual time=0.022..0.026 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
   ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=0) (actual time=0.013..0.013 rows=1 loops=1)
*/

note: (cost=0.00..22.52rows=1000width=32) (actual time=0.022..0.026 rows=10 loops=1)

Answer 2

The problem occurs when using the Set Returning Function in the SELECT target-list, but that way of calling these functions is kludgy in the first place.

See for example this thread on pg-hackers list on various reasons why experienced PG users tend to avoid it.

On the other hand, when the SRF is called in the FROM list, without the fake cross join, 9.1.6 produces the standard estimate too:

 explain analyze 
 select * from (select 'A' as foo) z, generate_series(1,10) b;

results in:

                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..22.52 rows=1000 width=36) (actual time=0.018..0.041 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Function Scan on generate_series b  (cost=0.00..12.50 rows=1000 width=4) (actual time=0.014..0.021 rows=10 loops=1)
 Total runtime: 0.076 ms
(4 rows)