By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
CREATE TABLE t
(
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
str TEXT NOT NULL
);
CREATE TABLE
CREATE INDEX t_ix ON t (str);
CREATE INDEX
SET enable_seqscan = OFF;
SET
Create or replace function random_string(length integer) returns text as
$$
declare
chars text[] := '{a,b,c,d,e,f,g,h}';
result text := '';
i integer := 0;
begin
if length < 0 then
raise exception 'Given length cannot be less than 0';
end if;
for i in 1..length loop
result := result || chars[1+random()*(array_length(chars, 1)-1)];
end loop;
return result;
end;
$$ language plpgsql;
CREATE FUNCTION
INSERT INTO t (str) VALUES
('abc');
INSERT 0 1
INSERT INTO t (str)
SELECT
random_string(3)
FROM
GENERATE_SERIES(1, 100000);
INSERT 0 100000
--
-- Snippet 8
--
SELECT str
= 'abc'
FROM t
WHERE str = 'abc'
LIMIT 10;
| ?column? |
|---|
| t |
| t |
| t |
| t |
| t |
| t |
| t |
| t |
| t |
| t |
SELECT 10
SELECT
id,
str
LIKE 'abc'
FROM t
LIMIT 5; -- LIMIT to avoid excessive scrolling in fiddle
| id | ?column? |
|---|---|
| 1 | t |
| 2 | f |
| 3 | f |
| 4 | f |
| 5 | f |
SELECT 5
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)
SELECT str
LIKE 'abc'
FROM t
| QUERY PLAN |
|---|
| Bitmap Heap Scan on public.t (cost=791.66..1937.92 rows=56261 width=1) (actual time=12.243..71.184 rows=100001 loops=1) |
| Output: (str ~~ 'abc'::text) |
| Heap Blocks: exact=443 |
| Buffers: shared hit=566 |
| -> Bitmap Index Scan on t_ix (cost=0.00..777.60 rows=56261 width=0) (actual time=12.171..12.171 rows=100001 loops=1) |
| Buffers: shared hit=123 |
| Settings: max_parallel_workers_per_gather = '4', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis', enable_seqscan = 'off' |
| Planning Time: 0.062 ms |
| Execution Time: 91.537 ms |
EXPLAIN
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)
SELECT str
LIKE 'abc'
FROM t
WHERE str = 'abc';
| QUERY PLAN |
|---|
| Bitmap Heap Scan on public.t (cost=6.47..420.47 rows=281 width=1) (actual time=0.039..0.177 rows=137 loops=1) |
| Output: (str ~~ 'abc'::text) |
| Recheck Cond: (t.str = 'abc'::text) |
| Heap Blocks: exact=112 |
| Buffers: shared hit=114 |
| -> Bitmap Index Scan on t_ix (cost=0.00..6.40 rows=281 width=0) (actual time=0.020..0.020 rows=137 loops=1) |
| Index Cond: (t.str = 'abc'::text) |
| Buffers: shared hit=2 |
| Settings: max_parallel_workers_per_gather = '4', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis', enable_seqscan = 'off' |
| Planning Time: 0.077 ms |
| Execution Time: 0.206 ms |
EXPLAIN
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)
SELECT str
LIKE 'a%'
FROM t;
| QUERY PLAN |
|---|
| Bitmap Heap Scan on public.t (cost=791.66..1937.92 rows=56261 width=1) (actual time=25.326..77.459 rows=100001 loops=1) |
| Output: (str ~~ 'a%'::text) |
| Heap Blocks: exact=443 |
| Buffers: shared hit=566 |
| -> Bitmap Index Scan on t_ix (cost=0.00..777.60 rows=56261 width=0) (actual time=25.257..25.257 rows=100001 loops=1) |
| Buffers: shared hit=123 |
| Settings: max_parallel_workers_per_gather = '4', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis', enable_seqscan = 'off' |
| Planning Time: 0.031 ms |
| Execution Time: 94.622 ms |
EXPLAIN
SELECT str
= 'a%' -- <<== the '%' part of the string is the literal character percent!
FROM t
LIMIT 10; -- limit to avoid excessive scrolling through fiddle!
| ?column? |
|---|
| f |
| f |
| f |
| f |
| f |
| f |
| f |
| f |
| f |
| f |
SELECT 10
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)
SELECT str
= 'a%'
FROM t;
| QUERY PLAN |
|---|
| Bitmap Heap Scan on public.t (cost=791.66..1937.92 rows=56261 width=1) (actual time=25.338..63.755 rows=100001 loops=1) |
| Output: (str = 'a%'::text) |
| Heap Blocks: exact=443 |
| Buffers: shared hit=566 |
| -> Bitmap Index Scan on t_ix (cost=0.00..777.60 rows=56261 width=0) (actual time=25.268..25.269 rows=100001 loops=1) |
| Buffers: shared hit=123 |
| Settings: max_parallel_workers_per_gather = '4', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis', enable_seqscan = 'off' |
| Planning Time: 0.029 ms |
| Execution Time: 85.128 ms |
EXPLAIN