mirror of
https://github.com/HypoPG/hypopg
synced 2026-05-24 09:38:21 +00:00
d102c8a640
Otherwise the emitted CREATE INDEX statement could be invalid. Thanks to Oliver Rice for the report.
57 lines
1.3 KiB
Text
57 lines
1.3 KiB
Text
-- hypothetical indexes using INCLUDE keyword, pg11+
|
|
-- Remove all the hypothetical indexes if any
|
|
SELECT hypopg_reset();
|
|
hypopg_reset
|
|
--------------
|
|
|
|
(1 row)
|
|
|
|
-- Make sure stats and visibility map are up to date
|
|
VACUUM ANALYZE hypo;
|
|
-- Should not use hypothetical index
|
|
-- Create normal index
|
|
SELECT COUNT(*) AS NB
|
|
FROM hypopg_create_index('CREATE INDEX ON hypo (id)');
|
|
nb
|
|
----
|
|
1
|
|
(1 row)
|
|
|
|
-- Should use hypothetical index using a regular Index Scan
|
|
SELECT COUNT(*) FROM do_explain('SELECT val FROM hypo WHERE id = 1') e
|
|
WHERE e ~ 'Index Scan.*<\d+>btree_hypo.*';
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
-- Remove all the hypothetical indexes
|
|
SELECT hypopg_reset();
|
|
hypopg_reset
|
|
--------------
|
|
|
|
(1 row)
|
|
|
|
-- Create INCLUDE index
|
|
SELECT COUNT(*) AS NB
|
|
FROM hypopg_create_index('CREATE INDEX ON hypo (id) INCLUDE (val, "Id2")');
|
|
nb
|
|
----
|
|
1
|
|
(1 row)
|
|
|
|
-- Should use hypothetical index using an Index Only Scan
|
|
SELECT COUNT(*) FROM do_explain('SELECT val FROM hypo WHERE id = 1') e
|
|
WHERE e ~ 'Index Only Scan.*<\d+>btree_hypo.*';
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
-- Deparse the index DDL
|
|
SELECT hypopg_get_indexdef(indexrelid) FROM hypopg();
|
|
hypopg_get_indexdef
|
|
-------------------------------------------------------------------
|
|
CREATE INDEX ON public.hypo USING btree (id) INCLUDE (val, "Id2")
|
|
(1 row)
|
|
|