Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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)
share|improve this answer
    
It might be kludgy but it is sometimes very useful and has been around for a long time on pg –  Jack Douglas Oct 16 '12 at 19:04
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)

share|improve this answer
    
Why is this working? –  dezso Oct 16 '12 at 12:04
    
because it only 'fails' if the generate_series is in the select–postgres's weird sort of accidental version of cross apply –  Jack Douglas Oct 16 '12 at 12:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.