clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 541799 fiddles created (11226 in the last week).

create table DEMO as select 1 n from generate_series(1,11) union all select 2 from generate_series(1,22) union all select 3 from generate_series(1,33) union all select 4 from generate_series(1,44) union all select 5 from generate_series(1,55) union all select 6 from generate_series(1,66) union all select 7 from generate_series(1,77) union all select 8 from generate_series(1,88) ;
396 rows affected
 hidden batch(es)


analyze DEMO;
 hidden batch(es)


select count(*),count(distinct n) from DEMO;
count count
396 8
 hidden batch(es)


prepare myselect (int) as select count(*) from DEMO where n=$1;
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(1);
QUERY PLAN
Aggregate (cost=6.98..6.99 rows=1 width=8) (actual time=0.073..0.073 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=11 width=0) (actual time=0.019..0.067 rows=11 loops=1)
Output: n
Filter: (demo.n = 1)
Rows Removed by Filter: 385
Buffers: shared hit=2
Planning Time: 0.151 ms
Execution Time: 0.116 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(2);
QUERY PLAN
Aggregate (cost=7.00..7.01 rows=1 width=8) (actual time=0.076..0.077 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=22 width=0) (actual time=0.022..0.069 rows=22 loops=1)
Output: n
Filter: (demo.n = 2)
Rows Removed by Filter: 374
Buffers: shared hit=2
Planning Time: 0.079 ms
Execution Time: 0.156 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(3);
QUERY PLAN
Aggregate (cost=7.03..7.04 rows=1 width=8) (actual time=0.076..0.076 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=33 width=0) (actual time=0.022..0.068 rows=33 loops=1)
Output: n
Filter: (demo.n = 3)
Rows Removed by Filter: 363
Buffers: shared hit=2
Planning Time: 0.069 ms
Execution Time: 0.099 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(4);
QUERY PLAN
Aggregate (cost=7.06..7.07 rows=1 width=8) (actual time=0.077..0.077 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=44 width=0) (actual time=0.025..0.068 rows=44 loops=1)
Output: n
Filter: (demo.n = 4)
Rows Removed by Filter: 352
Buffers: shared hit=2
Planning Time: 0.066 ms
Execution Time: 0.099 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(5);
QUERY PLAN
Aggregate (cost=7.09..7.10 rows=1 width=8) (actual time=0.079..0.079 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=55 width=0) (actual time=0.030..0.069 rows=55 loops=1)
Output: n
Filter: (demo.n = 5)
Rows Removed by Filter: 341
Buffers: shared hit=2
Planning Time: 0.064 ms
Execution Time: 0.101 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(6);
QUERY PLAN
Aggregate (cost=7.08..7.08 rows=1 width=8) (actual time=0.086..0.086 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=50 width=0) (actual time=0.037..0.075 rows=66 loops=1)
Output: n
Filter: (demo.n = $1)
Rows Removed by Filter: 330
Buffers: shared hit=2
Planning Time: 0.064 ms
Execution Time: 0.109 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(7);
QUERY PLAN
Aggregate (cost=7.08..7.08 rows=1 width=8) (actual time=0.088..0.089 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=50 width=0) (actual time=0.051..0.076 rows=77 loops=1)
Output: n
Filter: (demo.n = $1)
Rows Removed by Filter: 319
Buffers: shared hit=2
Planning Time: 0.009 ms
Execution Time: 0.111 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(8);
QUERY PLAN
Aggregate (cost=7.08..7.08 rows=1 width=8) (actual time=0.118..0.119 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=50 width=0) (actual time=0.102..0.111 rows=88 loops=1)
Output: n
Filter: (demo.n = $1)
Rows Removed by Filter: 308
Buffers: shared hit=2
Planning Time: 0.009 ms
Execution Time: 0.139 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(9);
QUERY PLAN
Aggregate (cost=7.08..7.08 rows=1 width=8) (actual time=0.071..0.071 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2
-> Seq Scan on fiddle_bkzzrmcwwrsnlmahugnb.demo (cost=0.00..6.95 rows=50 width=0) (actual time=0.069..0.069 rows=0 loops=1)
Output: n
Filter: (demo.n = $1)
Rows Removed by Filter: 396
Buffers: shared hit=2
Planning Time: 0.009 ms
Execution Time: 0.094 ms
 hidden batch(es)


select count(n),count(distinct n),count(n)::numeric/count(distinct n) "avg rows per value" from DEMO;
count count avg rows per value
396 8 49.5000000000000000
 hidden batch(es)