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
id | jsonb_pretty |
---|---|
1 | { "foo": { "key0": { "bar": "myValueA" }, "key1": { "bar": "myValueB" }, "keyN": { "bar": "myValueN" } } } |
2 | { "foo": { "key123": { "bar": "myValueC" }, "key345": { "bar": "myValueD" } } } |
3 | { "foo": { "XYZ": { "bar": "myValueN" } } } |
INSERT 0 3
id | myColumn |
---|---|
1 | {"foo": {"key0": {"bar": "myValueA"}, "key1": {"bar": "myValueB"}, "keyN": {"bar": "myValueN"}}} |
3 | {"foo": {"XYZ": {"bar": "myValueN"}}} |
SELECT 2
setseed |
---|
SELECT 1
INSERT 0 70000
CREATE INDEX
id | myColumn |
---|---|
232 | {"foo": [{"d18f655c3": {"bar": "myValueF"}}, {"d18f655c3": {"bar": "myValueB"}}, {"d18f65": {"bar": "myValueG"}}, {"key0": {"bar": "myValueJ"}}]} |
458 | {"foo": [{"8fecb20": {"bar": "myValueX"}}, {"8fec": {"bar": "myValueY"}}, {"8fecb": {"bar": "myValueD"}}, {"8fec": {"bar": "myValueX"}}, {"key1": {"bar": "myValueR"}}]} |
541 | {"foo": [{"04e": {"bar": "myValueC"}}, {"04ecb1f": {"bar": "myValueS"}}, {"04ecb1fa2": {"bar": "myValueV"}}, {"key1": {"bar": "myValueE"}}, {"04ecb1f": {"bar": "myValueO"}}, {"04ecb1fa": {"bar": "myValueP"}}]} |
1047 | {"foo": [{"b166b57d1": {"bar": "myValueI"}}, {"b166b": {"bar": "myValueH"}}, {"b166b57": {"bar": "myValueG"}}]} |
1439 | {"foo": [{"3df07f": {"bar": "myValueO"}}, {"3df0": {"bar": "myValueX"}}]} |
1840 | {"foo": [{"2f63": {"bar": "myValueL"}}, {"2f635a9f": {"bar": "myValueK"}}, {"2f635a9fe": {"bar": "myValueF"}}, {"2f635": {"bar": "myValueJ"}}, {"2f6": {"bar": "myValueQ"}}]} |
2377 | {"foo": [{"f76cb028": {"bar": "myValueP"}}, {"key3": {"bar": "myValueW"}}, {"f76cb028c": {"bar": "myValueR"}}, {"f76cb0": {"bar": "myValueP"}}, {"f76cb028c": {"bar": "myValueU"}}]} |
2684 | {"foo": [{"1f26": {"bar": "myValueR"}}, {"1f2627": {"bar": "myValueJ"}}]} |
3002 | {"foo": [{"e875ef": {"bar": "myValueF"}}, {"key5": {"bar": "myValueD"}}, {"e875effed": {"bar": "myValueQ"}}, {"e875eff": {"bar": "myValueI"}}, {"e875": {"bar": "myValueV"}}]} |
3434 | {"foo": [{"key2": {"bar": "myValueP"}}, {"bd11474c79": {"bar": "myValueF"}}, {"key3": {"bar": "myValueW"}}, {"bd11474c79": {"bar": "myValueN"}}, {"bd11": {"bar": "myValueO"}}]} |
SELECT 10
VACUUM
QUERY PLAN |
---|
Bitmap Heap Scan on public."myTable" (cost=150.48..2653.34 rows=14069 width=238) (actual time=3.226..24.910 rows=11649 loops=1) |
Output: id, "myColumn" |
Recheck Cond: ("myTable"."myColumn" @@ '($."foo".*."bar" == "myValueN")'::jsonpath) |
Heap Blocks: exact=2318 |
-> Bitmap Index Scan on "myTable_myColumn_idx" (cost=0.00..146.96 rows=14069 width=0) (actual time=2.918..2.918 rows=11649 loops=1) |
Index Cond: ("myTable"."myColumn" @@ '($."foo".*."bar" == "myValueN")'::jsonpath) |
Planning Time: 0.392 ms |
Execution Time: 25.548 ms |
EXPLAIN