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?.
CREATE TABLE
INSERT 0 11
CREATE INDEX
SET
search_on | show_trgm |
---|---|
abc123456789 | {" a"," ab",123,234,345,456,567,678,789,"89 ",abc,bc1,c12} |
123abc456789 | {" 1"," 12",123,23a,3ab,456,567,678,789,"89 ",abc,bc4,c45} |
123456789abc | {" 1"," 12",123,234,345,456,567,678,789,89a,9ab,abc,"bc "} |
abc | {" a"," ab",abc,"bc "} |
SELECT 4
id | search_on | comment | distance |
---|---|---|---|
10 | abc12 | leading short | 0.571429 |
6 | abcabcabc123 | leading, nested 2x | 0.7 |
11 | 12abc | trailing short | 0.75 |
4 | abc123abc456 | leading, nested 1x | 0.769231 |
1 | abc123456789 | leading | 0.785714 |
7 | 123abcabcabc | trailing nested 2x | 0.818182 |
5 | 123abc456abc | trailing,nested 1x | 0.857143 |
3 | 123456789abc | trailing | 0.866667 |
12 | 1abc2 | nested short | 0.888889 |
8 | 1abcabcabc23 | nested 3x | 0.916667 |
2 | 123abc456789 | nested | 0.9375 |
SELECT 11
id | search_on | comment | distance | prefix |
---|---|---|---|---|
10 | abc12 | leading short | 0.571429 | t |
6 | abcabcabc123 | leading, nested 2x | 0.7 | t |
4 | abc123abc456 | leading, nested 1x | 0.769231 | t |
1 | abc123456789 | leading | 0.785714 | t |
11 | 12abc | trailing short | 0.75 | f |
7 | 123abcabcabc | trailing nested 2x | 0.818182 | f |
5 | 123abc456abc | trailing,nested 1x | 0.857143 | f |
3 | 123456789abc | trailing | 0.866667 | f |
12 | 1abc2 | nested short | 0.888889 | f |
8 | 1abcabcabc23 | nested 3x | 0.916667 | f |
2 | 123abc456789 | nested | 0.9375 | f |
SELECT 11
id | search_on | comment | distance | prefix | suffix |
---|---|---|---|---|---|
10 | abc12 | leading short | 0.571429 | t | f |
6 | abcabcabc123 | leading, nested 2x | 0.7 | t | f |
4 | abc123abc456 | leading, nested 1x | 0.769231 | t | f |
1 | abc123456789 | leading | 0.785714 | t | f |
11 | 12abc | trailing short | 0.75 | f | t |
7 | 123abcabcabc | trailing nested 2x | 0.818182 | f | t |
5 | 123abc456abc | trailing,nested 1x | 0.857143 | f | t |
3 | 123456789abc | trailing | 0.866667 | f | t |
12 | 1abc2 | nested short | 0.888889 | f | f |
8 | 1abcabcabc23 | nested 3x | 0.916667 | f | f |
2 | 123abc456789 | nested | 0.9375 | f | f |
SELECT 11