> That code could be modified to swap the tuplestores and do a
> tuplestore_clear() instead of tuplestore_end() followed by
> tuplestore_begin_heap().
>
> It's likely worthwhile from a performance point of view. Here's a
> small test as an example:
>
> master:
> postgres=# with recursive cte (a) as (select 1 union all select
> cte.a+1 from cte where cte.a+1 <= 1000000) select count(*) from cte;
> Time: 219.023 ms
> Time: 218.828 ms
> Time: 219.093 ms
>
> with attached patched:
> postgres=# with recursive cte (a) as (select 1 union all select
> cte.a+1 from cte where cte.a+1 <= 1000000) select count(*) from cte;
> Time: 169.734 ms
> Time: 164.841 ms
> Time: 169.168 ms
Impressive result. I also ran your query with count 1000.
without the patch:
Time: 3.655 ms
Time: 4.123 ms
Time: 2.163 ms
wit the patch:
Time: 3.641 ms
Time: 2.356 ms
Time: 2.347 ms
It seems with the patch the performance is slightly better or almost
same. I think the patch improves the performance without sacrificing
the smaller iteration case.
Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
