mirror of
https://github.com/HypoPG/hypopg
synced 2026-05-24 09:38:21 +00:00
73d82eee94
The estimated index size depends on the architecture, so just make sure the code path is tested, without really caring of what the estimated size is. Thanks to Christoph Berg for the report.
102 lines
3 KiB
PL/PgSQL
102 lines
3 KiB
PL/PgSQL
-- SETUP
|
|
CREATE OR REPLACE FUNCTION do_explain(stmt text) RETURNS table(a text) AS
|
|
$_$
|
|
DECLARE
|
|
ret text;
|
|
BEGIN
|
|
FOR ret IN EXECUTE format('EXPLAIN (FORMAT text) %s', stmt) LOOP
|
|
a := ret;
|
|
RETURN next ;
|
|
END LOOP;
|
|
END;
|
|
$_$
|
|
LANGUAGE plpgsql;
|
|
|
|
CREATE EXTENSION hypopg;
|
|
|
|
CREATE TABLE hypo (id integer, val text);
|
|
|
|
INSERT INTO hypo SELECT i, 'line ' || i
|
|
FROM generate_series(1,100000) f(i);
|
|
|
|
ANALYZE hypo;
|
|
|
|
-- TESTS
|
|
SELECT COUNT(*) AS nb
|
|
FROM public.hypopg_create_index('SELECT 1;CREATE INDEX ON hypo(id); SELECT 2');
|
|
|
|
SELECT nspname, relname, amname FROM public.hypopg_list_indexes();
|
|
|
|
-- Should use hypothetical index
|
|
SELECT COUNT(*) FROM do_explain('SELECT * FROM hypo WHERE id = 1') e
|
|
WHERE e ~ 'Index.*<\d+>btree_hypo.*';
|
|
|
|
-- Should use hypothetical index
|
|
SELECT COUNT(*) FROM do_explain('SELECT * FROM hypo ORDER BY id') e
|
|
WHERE e ~ 'Index.*<\d+>btree_hypo.*';
|
|
|
|
-- Should not use hypothetical index
|
|
SELECT COUNT(*) FROM do_explain('SELECT * FROM hypo') e
|
|
WHERE e ~ 'Index.*<\d+>btree_hypo.*';
|
|
|
|
-- Add predicate index
|
|
SELECT COUNT(*) AS nb
|
|
FROM public.hypopg_create_index('CREATE INDEX ON hypo(id) WHERE id < 5');
|
|
|
|
-- This specific index should be used
|
|
WITH ind AS (
|
|
SELECT indexrelid, row_number() OVER (ORDER BY indexrelid) AS num
|
|
FROM public.hypopg()
|
|
),
|
|
regexp AS (
|
|
SELECT regexp_replace(e, '.*<(\d+)>.*', E'\\1', 'g') AS r
|
|
FROM do_explain('SELECT * FROM hypo WHERE id < 3') AS e
|
|
)
|
|
|
|
SELECT num
|
|
FROM ind
|
|
JOIN regexp ON ind.indexrelid::text = regexp.r;
|
|
|
|
-- Specify fillfactor
|
|
SELECT COUNT(*) AS NB
|
|
FROM public.hypopg_create_index('CREATE INDEX ON hypo(id) WITH (fillfactor = 10)');
|
|
|
|
-- Specify an incorrect fillfactor
|
|
SELECT COUNT(*) AS NB
|
|
FROM public.hypopg_create_index('CREATE INDEX ON hypo(id) WITH (fillfactor = 1)');
|
|
|
|
-- Index size estimation
|
|
SELECT hypopg_relation_size(indexrelid) = current_setting('block_size')::bigint AS one_block
|
|
FROM hypopg()
|
|
ORDER BY indexrelid;
|
|
|
|
-- locally disable hypoopg
|
|
SET hypopg.enabled to false;
|
|
|
|
-- no hypothetical index should be used
|
|
SELECT COUNT(*) FROM do_explain('SELECT * FROM hypo WHERE id = 1') e
|
|
WHERE e ~ 'Index.*<\d+>btree_hypo.*';
|
|
|
|
-- locally re-enable hypoopg
|
|
SET hypopg.enabled to true;
|
|
|
|
-- hypothetical index should be used
|
|
SELECT COUNT(*) FROM do_explain('SELECT * FROM hypo WHERE id = 1') e
|
|
WHERE e ~ 'Index.*<\d+>btree_hypo.*';
|
|
|
|
-- Remove one hypothetical index
|
|
SELECT hypopg_drop_index(indexrelid) FROM hypopg() ORDER BY indexrelid LIMIT 1;
|
|
|
|
-- Remove all the hypothetical indexes
|
|
SELECT hypopg_reset();
|
|
|
|
-- index on expression
|
|
SELECT COUNT(*) AS NB
|
|
FROM public.hypopg_create_index('CREATE INDEX ON hypo (md5(val))');
|
|
|
|
-- Should use hypothetical index
|
|
SELECT COUNT(*) FROM do_explain('SELECT * FROM hypo WHERE md5(val) = md5(''line 1'')') e
|
|
WHERE e ~ 'Index.*<\d+>btree_hypo.*';
|
|
|
|
-- Deparse an index DDL, with almost every possible pathcode
|
|
SELECT hypopg_get_indexdef(indexrelid) FROM hypopg_create_index('create index on hypo using btree(id desc, id desc nulls first, id desc nulls last, cast(md5(val) as bpchar) bpchar_pattern_ops) with (fillfactor = 10) WHERE id < 1000 AND id +1 %2 = 3');
|