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 | { "type": [ "RETAIL" ], "status": [ "ACTIVE" ], "endDate": [ "2024-02-20T21:00:00.000Z" ], "currency": [ "USD" ], "countries": [ "US", "IT", "ES" ], "startDate": [ "2024-02-13T08:00:00.000Z" ], "categories": [ "ELECTRONICS" ] } |
INSERT 0 1
| id |
|---|
SELECT 0
| setseed |
|---|
SELECT 1
INSERT 0 39999
| id | attributes |
|---|---|
| 292 | {"type": [["BULK"]], "status": [["ACTIVE", "ON HOLD", "NOT ACTIVE"]], "endDate": [], "currency": [["CAD", "GBP", "EUR"]], "countries": [["MX", "CA"]], "startDate": [["2024-03-16T00:16:47.983392+00:00"]], "categories": [["FMG"]]} |
| 341 | {"type": [["RETAIL"]], "status": [["ACTIVE", "ON HOLD"]], "endDate": [], "currency": [["CAD", "GBP", "EUR", "RMB"]], "countries": [["MX"]], "startDate": [["2023-06-25T23:38:48.146592+00:00", "2023-05-10T00:34:16.015392+00:00", "2023-10-14T16:45:42.578592+00:00", "2023-06-17T17:49:27.160992+00:00"]], "categories": [["ELECTRONICS"]]} |
| 885 | {"type": [["RETAIL"]], "status": [["ON HOLD"]], "endDate": [], "currency": [["USD", "CAD", "GBP"]], "countries": [["MX", "CA", "UK"]], "startDate": [["2023-07-24T05:31:57.919392+00:00", "2023-11-21T23:51:45.832992+00:00", "2024-01-25T23:18:23.080992+00:00", "2024-02-15T04:55:05.560992+00:00"]], "categories": [["FMG"]]} |
| 1136 | {"type": [["RETAIL", "BULK"]], "status": [["ACTIVE", "ON HOLD"]], "endDate": [], "currency": [["USD", "CAD", "GBP", "EUR", "RMB"]], "countries": [["US", "MX"]], "startDate": [["2023-08-12T14:05:58.053792+00:00", "2023-10-16T21:03:51.852192+00:00", "2023-09-01T02:45:23.685792+00:00", "2023-12-19T20:00:37.768992+00:00"]], "categories": [["FMG"]]} |
| 1646 | {"type": [["RETAIL", "BULK"]], "status": [["ACTIVE", "ON HOLD"]], "endDate": [], "currency": [["CAD", "GBP", "EUR", "RMB", "JPY"]], "countries": [["US", "MX", "CA", "UK", "FR"]], "startDate": [["2023-09-06T22:14:31.500192+00:00", "2023-05-22T05:04:42.799392+00:00", "2023-11-16T11:43:47.263392+00:00", "2023-06-17T13:14:40.399392+00:00"]], "categories": [["ELECTRONICS", "FMG", "COSMETICS"]]} |
| 1839 | {"type": [["BULK"]], "status": [[]], "endDate": [], "currency": [["USD", "CAD", "GBP"]], "countries": [["US", "MX", "CA"]], "startDate": [["2023-06-02T04:57:06.088992+00:00", "2024-01-06T13:25:50.431392+00:00", "2023-04-07T11:07:57.717792+00:00", "2023-09-18T11:21:10.092192+00:00", "2023-07-28T06:28:54.952992+00:00"]], "categories": [["ELECTRONICS", "FMG"]]} |
| 2459 | {"type": [["RETAIL", "BULK"]], "status": [["ACTIVE", "ON HOLD", "NOT ACTIVE"]], "endDate": [], "currency": [["USD", "CAD"]], "countries": [["MX", "CA", "UK"]], "startDate": [["2023-09-26T04:44:30.088992+00:00", "2023-04-19T19:45:03.698592+00:00", "2023-07-29T17:26:27.612192+00:00", "2023-03-20T19:23:35.820192+00:00", "2024-01-06T19:13:40.437792+00:00"]], "categories": [["FMG", "COSMETICS"]]} |
| 2838 | {"type": [[]], "status": [["ACTIVE", "ON HOLD"]], "endDate": [], "currency": [["CAD"]], "countries": [["MX", "CA", "UK"]], "startDate": [["2023-09-15T12:30:15.650592+00:00"]], "categories": [["ELECTRONICS"]]} |
| 2846 | {"type": [["RETAIL", "BULK"]], "status": [["ACTIVE", "ON HOLD"]], "endDate": [], "currency": [["USD", "CAD", "GBP", "EUR", "RMB", "JPY"]], "countries": [["US", "MX", "CA"]], "startDate": [["2023-12-22T20:53:16.207392+00:00"]], "categories": [["FMG"]]} |
| 2945 | {"type": [["RETAIL", "BULK"]], "status": [["ACTIVE", "ON HOLD"]], "endDate": [], "currency": [["USD"]], "countries": [["US", "MX", "CA"]], "startDate": [["2024-02-07T02:28:29.781792+00:00", "2023-08-27T20:28:12.415392+00:00", "2023-12-01T00:36:32.527392+00:00"]], "categories": [["FMG"]]} |
| 2967 | {"type": [["RETAIL", "BULK"]], "status": [["ON HOLD"]], "endDate": [], "currency": [["CAD"]], "countries": [["US", "MX", "CA", "UK", "FR"]], "startDate": [["2023-12-31T05:25:38.796192+00:00", "2023-10-17T13:35:34.927392+00:00", "2023-12-06T10:08:09.871392+00:00"]], "categories": [[]]} |
| 3031 | {"type": [["RETAIL"]], "status": [["ON HOLD"]], "endDate": [], "currency": [["USD", "CAD"]], "countries": [["US"]], "startDate": [["2023-05-17T10:28:18.348192+00:00", "2023-07-26T22:01:10.226592+00:00", "2023-06-09T07:30:47.042592+00:00", "2023-07-30T09:19:48.818592+00:00"]], "categories": [["ELECTRONICS"]]} |
| 3247 | {"type": [["RETAIL", "BULK"]], "status": [[]], "endDate": [], "currency": [["CAD", "GBP", "EUR"]], "countries": [["US", "MX", "CA", "UK", "FR"]], "startDate": [["2023-08-01T16:44:33.544992+00:00", "2024-02-15T00:41:48.492192+00:00", "2024-03-12T14:50:33.429792+00:00"]], "categories": [["FMG"]]} |
| 3433 | {"type": [["BULK"]], "status": [["ACTIVE"]], "endDate": [], "currency": [["USD", "CAD", "GBP", "EUR"]], "countries": [["MX", "CA", "UK", "FR"]], "startDate": [["2023-11-09T00:00:19.826592+00:00", "2023-06-08T11:24:59.311392+00:00", "2024-03-01T12:00:53.263392+00:00", "2023-09-02T20:36:30.079392+00:00", "2023-03-22T18:02:23.464992+00:00"]], "categories": [["ELECTRONICS", "FMG"]]} |
| 3648 | {"type": [["RETAIL", "BULK"]], "status": [[]], "endDate": [], "currency": [["USD", "CAD", "GBP", "EUR", "RMB"]], "countries": [["US", "MX", "CA", "UK", "FR"]], "startDate": [["2024-01-13T13:29:30.405792+00:00", "2023-08-14T14:07:21.170592+00:00", "2023-12-15T22:14:38.412192+00:00", "2023-10-21T06:32:39.506592+00:00", "2023-09-21T09:33:13.980192+00:00"]], "categories": [["FMG"]]} |
| 3661 | {"type": [["RETAIL", "BULK"]], "status": [["ACTIVE"]], "endDate": [], "currency": [["USD"]], "countries": [["US", "MX"]], "startDate": [["2023-11-15T22:13:37.327392+00:00", "2023-07-20T22:13:20.306592+00:00", "2024-01-05T09:36:20.863392+00:00", "2024-02-29T22:57:07.384992+00:00", "2023-12-23T00:23:06.242592+00:00"]], "categories": [["ELECTRONICS"]]} |
| 3672 | {"type": [["BULK"]], "status": [["ACTIVE", "ON HOLD"]], "endDate": [], "currency": [["CAD", "GBP", "EUR", "RMB", "JPY"]], "countries": [["US", "MX"]], "startDate": [["2024-01-20T08:50:19.087392+00:00", "2024-02-16T18:04:28.399392+00:00"]], "categories": [["FMG"]]} |
| 4127 | {"type": [["RETAIL", "BULK"]], "status": [["ON HOLD", "NOT ACTIVE"]], "endDate": [], "currency": [["CAD", "GBP", "EUR", "RMB"]], "countries": [["US", "MX", "CA", "UK"]], "startDate": [["2023-10-17T12:04:31.682592+00:00"]], "categories": [["ELECTRONICS", "FMG"]]} |
| 4735 | {"type": [["BULK"]], "status": [["ON HOLD"]], "endDate": [], "currency": [["CAD"]], "countries": [["MX", "CA", "UK", "FR"]], "startDate": [["2023-12-22T07:48:27.429792+00:00"]], "categories": [["ELECTRONICS"]]} |
| 5024 | {"type": [["BULK"]], "status": [["ON HOLD"]], "endDate": [], "currency": [["USD"]], "countries": [["US", "MX", "CA", "UK"]], "startDate": [["2024-02-16T23:21:21.669792+00:00"]], "categories": [["ELECTRONICS", "FMG", "COSMETICS"]]} |
SELECT 20
CREATE INDEX
VACUUM
| QUERY PLAN |
|---|
| Bitmap Heap Scan on public.products (cost=95.40..2370.54 rows=10091 width=5) (actual time=4.523..56.227 rows=9708 loops=1) |
| Output: id |
| Recheck Cond: (products.attributes @@ '(exists ($."countries"[*]?(@ == "US" || @ == "MX")) && exists ($."startDate"?(@ > "2024-02-12T07:00:00.000Z")))'::jsonpath) |
| Rows Removed by Index Recheck: 28323 |
| Heap Blocks: exact=2149 |
| -> Bitmap Index Scan on products_attributes_idx (cost=0.00..92.88 rows=10091 width=0) (actual time=4.257..4.257 rows=38031 loops=1) |
| Index Cond: (products.attributes @@ '(exists ($."countries"[*]?(@ == "US" || @ == "MX")) && exists ($."startDate"?(@ > "2024-02-12T07:00:00.000Z")))'::jsonpath) |
| Planning Time: 0.359 ms |
| Execution Time: 56.679 ms |
EXPLAIN
| id | attributes |
|---|---|
| 1 | {"type": ["RETAIL"], "status": ["ACTIVE"], "endDate": ["2024-02-20T21:00:00.000Z"], "currency": ["USD"], "countries": ["US", "IT", "ES"], "startDate": ["2024-02-13T08:00:00.000Z"], "categories": ["ELECTRONICS"]} |
| 4 | {"type": [["BULK"]], "status": [["ACTIVE", "ON HOLD", "NOT ACTIVE"]], "endDate": [], "currency": [["USD", "CAD", "GBP", "EUR", "RMB"]], "countries": [["MX"]], "startDate": [["2024-03-10T05:32:32.652192+00:00"]], "categories": [["ELECTRONICS"]]} |
| 5 | {"type": [["RETAIL", "BULK"]], "status": [[]], "endDate": [], "currency": [["CAD", "GBP", "EUR", "RMB", "JPY"]], "countries": [["US", "MX", "CA"]], "startDate": [["2024-03-09T05:46:11.983392+00:00", "2023-04-25T10:25:36.780192+00:00", "2024-03-09T10:42:07.096992+00:00", "2023-11-07T09:05:28.533792+00:00", "2023-12-11T06:09:23.628192+00:00", "2023-09-02T08:59:27.727392+00:00"]], "categories": [["ELECTRONICS", "FMG"]]} |
| 13 | {"type": [["RETAIL"]], "status": [[]], "endDate": [], "currency": [["USD", "CAD", "GBP", "EUR"]], "countries": [["US", "MX", "CA", "UK", "FR"]], "startDate": [["2023-11-17T06:21:30.770592+00:00", "2024-01-31T08:40:25.605792+00:00", "2023-07-29T22:46:07.720992+00:00", "2024-02-14T12:59:40.284192+00:00", "2023-10-13T05:55:23.042592+00:00"]], "categories": [["ELECTRONICS", "FMG", "COSMETICS"]]} |
| 20 | {"type": [["RETAIL", "BULK"]], "status": [["ACTIVE", "ON HOLD"]], "endDate": [], "currency": [["CAD", "GBP"]], "countries": [["MX", "CA", "UK"]], "startDate": [["2023-10-30T11:45:14.700192+00:00", "2023-05-05T13:36:08.882592+00:00", "2024-03-15T11:31:46.168992+00:00", "2023-10-12T06:39:43.989792+00:00"]], "categories": [["ELECTRONICS", "FMG"]]} |
SELECT 5