load 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = ON;
SET auto_explain.log_buffers = ON;
SET auto_explain.log_nested_statements = ON;

select version();

/*
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)
*/

create table t as select n,'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz' txt from GENERATE_SERIES(1, 1000000) as t(n);
analyze t;

explain analyze select count(*) from t;
/*
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=15554.55..15554.56 rows=1 width=8) (actual time=210.428..210.428 rows=1 loops=1)
   ->  Gather  (cost=15554.33..15554.54 rows=2 width=8) (actual time=210.332..210.420 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=14554.33..14554.34 rows=1 width=8) (actual time=202.036..202.036 rows=1 loops=3)
               ->  Parallel Seq Scan on t  (cost=0.00..13512.67 rows=416667 width=0) (actual time=0.061..133.659 rows=333333 loops=3)
 Planning time: 0.144 ms
 Execution time: 219.291 ms
(8 rows)
*/

CREATE OR REPLACE FUNCTION count_t()
RETURNS table(count bigint) AS
$body$
DECLARE
BEGIN
return query
select count(*) from t;
         
RETURN;

END
$body$
LANGUAGE 'plpgsql'
STABLE
CALLED ON NULL INPUT
SECURITY INVOKER
parallel safe
COST 10000;

select * from count_t();

/*
< 2018-02-27 08:47:03 EST:ignitemart:igniteadmin:27190 >LOG:  duration: 458.878 ms  plan:
        Query Text: select count(*) from t
        Aggregate  (cost=21846.00..21846.01 rows=1 width=8) (actual time=458.864..458.864 rows=1 loops=1)
          Buffers: shared hit=9346
          ->  Seq Scan on t  (cost=0.00..19346.00 rows=1000000 width=0) (actual time=0.027..250.312 rows=1000000 loops=1)
                Buffers: shared hit=9346
*/

CREATE OR REPLACE FUNCTION count_t_sql()
RETURNS table(count bigint) AS
$body$

select count(*) from t;
         
$body$
LANGUAGE sql
STABLE
CALLED ON NULL INPUT
SECURITY INVOKER
parallel safe
COST 100000;

select * from count_t_sql();

/*
< 2018-02-27 08:47:07 EST:ignitemart:igniteadmin:27190 >LOG:  duration: 200.076 ms  plan:
        Query Text: select * from count_t_sql();
        Finalize Aggregate  (cost=15554.55..15554.56 rows=1 width=8) (actual time=192.274..192.274 rows=1 loops=1)
          Buffers: shared hit=3721
          ->  Gather  (cost=15554.33..15554.54 rows=2 width=8) (actual time=192.222..192.266 rows=3 loops=1)
                Workers Planned: 2
                Workers Launched: 2
                Buffers: shared hit=3721
                ->  Partial Aggregate  (cost=14554.33..14554.34 rows=1 width=8) (actual time=184.039..184.039 rows=1 loops=3)
                      Buffers: shared hit=9346
                      ->  Parallel Seq Scan on t  (cost=0.00..13512.67 rows=416667 width=0) (actual time=0.032..117.308 rows=333333 loops=3)
                            Buffers: shared hit=9346
*/