add remove language chart show hidden hide
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE
INSERT 0 100000
CREATE TABLE
INSERT 0 10000
VACUUM
QUERY PLAN
HashAggregate (cost=5827.33..5827.34 rows=1 width=20) (actual time=93.511..93.511 rows=3 loops=1)
  Group Key: s.lab_name
  -> Nested Loop (cost=0.29..5827.33 rows=1 width=20) (actual time=0.076..89.740 rows=12561 loops=1)
        -> Seq Scan on measurements (cost=0.00..5819.00 rows=1 width=105) (actual time=0.060..63.277 rows=12561 loops=1)
              Filter: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
              Rows Removed by Filter: 87439
        -> Index Scan using scientist_labs_pkey on scientist_labs s (cost=0.29..8.32 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=12561)
              Index Cond: (scientist_id = ((measurements.record ->> 'scientist_id'::text))::bigint)
Planning time: 0.941 ms
Execution time: 93.800 ms
EXPLAIN
CREATE INDEX
CREATE INDEX
CREATE INDEX
ANALYZE
QUERY PLAN
HashAggregate (cost=5796.85..5796.88 rows=3 width=20) (actual time=35.624..35.624 rows=3 loops=1)
  Group Key: s.lab_name
  -> Hash Join (cost=2153.33..5735.05 rows=12361 width=20) (actual time=10.644..33.201 rows=12561 loops=1)
        Hash Cond: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
        -> Bitmap Heap Scan on measurements (cost=1857.33..4666.49 rows=12361 width=105) (actual time=8.153..20.414 rows=12561 loops=1)
              Recheck Cond: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
              Filter: (((record ->> 'value_2'::text))::integer = 0)
              Rows Removed by Filter: 12284
              Heap Blocks: exact=1819
              -> BitmapAnd (cost=1857.33..1857.33 rows=24754 width=0) (actual time=7.948..7.948 rows=0 loops=1)
                    -> Bitmap Index Scan on measurements_int4_idx (cost=0.00..924.62 rows=49643 width=0) (actual time=3.890..3.890 rows=49874 loops=1)
                          Index Cond: (((record ->> 'value_1'::text))::integer = 0)
                    -> Bitmap Index Scan on measurements_int4_idx2 (cost=0.00..926.27 rows=49863 width=0) (actual time=3.919..3.919 rows=50015 loops=1)
                          Index Cond: (((record ->> 'value_3'::text))::integer = 0)
        -> Hash (cost=171.00..171.00 rows=10000 width=28) (actual time=2.449..2.449 rows=10000 loops=1)
              Buckets: 1024 Batches: 1 Memory Usage: 599kB
              -> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.003..0.950 rows=10000 loops=1)
Planning time: 0.736 ms
Execution time: 35.884 ms
EXPLAIN