By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
| setseed |
|---|
SELECT 1
SELECT 200000
CREATE INDEX
ERROR: syntax error at or near "/" LINE 2: (SELECT COUNT(*) FROM tbl WHERE col_name > .5) / (SELECT COU... ^
| ?column? |
|---|
| 0.500455 |
SELECT 1
| QUERY PLAN |
|---|
| Result (cost=6545.12..6545.13 rows=1 width=8) (actual time=42.603..42.606 rows=1 loops=1) |
| Output: ($0 / ($1)::double precision) |
| InitPlan 1 (returns $0) |
| -> Aggregate (cost=3149.09..3149.11 rows=1 width=8) (actual time=20.745..20.747 rows=1 loops=1) |
| Output: (count(*))::double precision |
| -> Bitmap Heap Scan on public.tbl (cost=1253.09..2982.43 rows=66667 width=0) (actual time=5.720..16.324 rows=100091 loops=1) |
| Recheck Cond: (tbl.col_name > '0.5'::double precision) |
| Heap Blocks: exact=885 |
| -> Bitmap Index Scan on tbl_col_name_idx (cost=0.00..1236.42 rows=66667 width=0) (actual time=5.621..5.622 rows=100091 loops=1) |
| Index Cond: (tbl.col_name > '0.5'::double precision) |
| InitPlan 2 (returns $1) |
| -> Aggregate (cost=3396.00..3396.01 rows=1 width=8) (actual time=21.850..21.851 rows=1 loops=1) |
| Output: count(*) |
| -> Seq Scan on public.tbl tbl_1 (cost=0.00..2896.00 rows=200000 width=0) (actual time=0.010..13.094 rows=200000 loops=1) |
| Output: tbl_1.col_name |
| Planning Time: 0.131 ms |
| Execution Time: 42.640 ms |
EXPLAIN
| ?column? |
|---|
| 0.500455 |
SELECT 1
| QUERY PLAN |
|---|
| Aggregate (cost=4396.00..4396.02 rows=1 width=8) (actual time=27.873..27.874 rows=1 loops=1) |
| Output: ((count(*) FILTER (WHERE (col_name > '0.5'::double precision)))::double precision / (count(*))::double precision) |
| -> Seq Scan on public.tbl (cost=0.00..2896.00 rows=200000 width=8) (actual time=0.007..13.235 rows=200000 loops=1) |
| Output: col_name |
| Planning Time: 0.067 ms |
| Execution Time: 27.894 ms |
EXPLAIN
| ?column? |
|---|
| 0.50045500000000000000 |
SELECT 1
| QUERY PLAN |
|---|
| Aggregate (cost=6545.11..6545.12 rows=1 width=32) (actual time=42.530..42.532 rows=1 loops=1) |
| Output: ($0 / (count(*))::numeric) |
| InitPlan 1 (returns $0) |
| -> Aggregate (cost=3149.09..3149.11 rows=1 width=32) (actual time=20.708..20.709 rows=1 loops=1) |
| Output: (count(*))::numeric |
| -> Bitmap Heap Scan on public.tbl tbl_1 (cost=1253.09..2982.43 rows=66667 width=0) (actual time=5.856..16.328 rows=100091 loops=1) |
| Recheck Cond: (tbl_1.col_name > '0.5'::double precision) |
| Heap Blocks: exact=885 |
| -> Bitmap Index Scan on tbl_col_name_idx (cost=0.00..1236.42 rows=66667 width=0) (actual time=5.757..5.757 rows=100091 loops=1) |
| Index Cond: (tbl_1.col_name > '0.5'::double precision) |
| -> Seq Scan on public.tbl (cost=0.00..2896.00 rows=200000 width=0) (actual time=0.007..13.083 rows=200000 loops=1) |
| Output: tbl.col_name |
| Planning Time: 0.112 ms |
| Execution Time: 42.565 ms |
EXPLAIN
| avg |
|---|
| 0.50045500000000000000 |
SELECT 1
| QUERY PLAN |
|---|
| Aggregate (cost=3896.00..3896.01 rows=1 width=32) (actual time=31.898..31.899 rows=1 loops=1) |
| Output: avg(CASE WHEN (col_name > '0.5'::double precision) THEN 1.0 ELSE 0.0 END) |
| -> Seq Scan on public.tbl (cost=0.00..2896.00 rows=200000 width=8) (actual time=0.007..13.553 rows=200000 loops=1) |
| Output: col_name |
| Planning Time: 0.061 ms |
| Execution Time: 31.919 ms |
EXPLAIN