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. 541813 fiddles created (11240 in the last week).

create table DEMO(n int) partition by list(n); create table DEMO1 partition of DEMO for values in (1); create table DEMO2 partition of DEMO for values in (2); create table DEMO3 partition of DEMO for values in (3); create table DEMO4 partition of DEMO for values in (4); create table DEMO5 partition of DEMO for values in (5); create table DEMO6 partition of DEMO for values in (6); create table DEMO7 partition of DEMO for values in (7); create table DEMO8 partition of DEMO for values in (8);
 hidden batch(es)


insert into DEMO 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=1.17..1.18 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Seq Scan on fiddle_fjgqpwfnhhwblwnhxefr.demo1 (cost=0.00..1.14 rows=11 width=0) (actual time=0.014..0.018 rows=11 loops=1)
Filter: (demo1.n = 1)
Buffers: shared hit=1
Planning Time: 0.223 ms
Execution Time: 0.070 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(2);
QUERY PLAN
Aggregate (cost=1.33..1.34 rows=1 width=8) (actual time=0.030..0.030 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Seq Scan on fiddle_fjgqpwfnhhwblwnhxefr.demo2 (cost=0.00..1.27 rows=22 width=0) (actual time=0.015..0.022 rows=22 loops=1)
Filter: (demo2.n = 2)
Buffers: shared hit=1
Planning Time: 0.143 ms
Execution Time: 0.060 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(3);
QUERY PLAN
Aggregate (cost=1.50..1.51 rows=1 width=8) (actual time=0.034..0.035 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Seq Scan on fiddle_fjgqpwfnhhwblwnhxefr.demo3 (cost=0.00..1.41 rows=33 width=0) (actual time=0.016..0.025 rows=33 loops=1)
Filter: (demo3.n = 3)
Buffers: shared hit=1
Planning Time: 0.124 ms
Execution Time: 0.064 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(4);
QUERY PLAN
Aggregate (cost=1.66..1.67 rows=1 width=8) (actual time=0.039..0.040 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Seq Scan on fiddle_fjgqpwfnhhwblwnhxefr.demo4 (cost=0.00..1.55 rows=44 width=0) (actual time=0.016..0.028 rows=44 loops=1)
Filter: (demo4.n = 4)
Buffers: shared hit=1
Planning Time: 0.124 ms
Execution Time: 0.069 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(5);
QUERY PLAN
Aggregate (cost=1.82..1.83 rows=1 width=8) (actual time=0.043..0.043 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Seq Scan on fiddle_fjgqpwfnhhwblwnhxefr.demo5 (cost=0.00..1.69 rows=55 width=0) (actual time=0.016..0.030 rows=55 loops=1)
Filter: (demo5.n = 5)
Buffers: shared hit=1
Planning Time: 0.123 ms
Execution Time: 0.072 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(6);
QUERY PLAN
Aggregate (cost=1.99..2.00 rows=1 width=8) (actual time=0.049..0.049 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Seq Scan on fiddle_fjgqpwfnhhwblwnhxefr.demo6 (cost=0.00..1.83 rows=66 width=0) (actual time=0.017..0.034 rows=66 loops=1)
Filter: (demo6.n = 6)
Buffers: shared hit=1
Planning Time: 0.347 ms
Execution Time: 0.078 ms
 hidden batch(es)


set plan_cache_mode=force_generic_plan
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(7);
QUERY PLAN
Aggregate (cost=15.92..15.93 rows=1 width=8) (actual time=0.063..0.063 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Append (cost=0.00..14.93 rows=396 width=0) (actual time=0.015..0.049 rows=77 loops=1)
Buffers: shared hit=1
Subplans Removed: 7
-> Seq Scan on fiddle_fjgqpwfnhhwblwnhxefr.demo7 (cost=0.00..1.96 rows=77 width=0) (actual time=0.014..0.034 rows=77 loops=1)
Filter: (demo7.n = $1)
Buffers: shared hit=1
Planning Time: 0.016 ms
Execution Time: 0.133 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) execute myselect(8);
QUERY PLAN
Aggregate (cost=15.92..15.93 rows=1 width=8) (actual time=0.037..0.037 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Append (cost=0.00..14.93 rows=396 width=0) (actual time=0.008..0.029 rows=88 loops=1)
Buffers: shared hit=1
Subplans Removed: 7
-> Seq Scan on fiddle_fjgqpwfnhhwblwnhxefr.demo8 (cost=0.00..2.10 rows=88 width=0) (actual time=0.008..0.020 rows=88 loops=1)
Filter: (demo8.n = $1)
Buffers: shared hit=1
Planning Time: 0.007 ms
Execution Time: 0.056 ms
 hidden batch(es)