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?.
| somedata | data |
|---|---|
| somedata | {"Limits": {"fields": [{"key": "DAILY_LIMITS", "value": "1559", "lastModified": 1543857829148}, {"key": "MONTHLY_LIMIT", "value": "25590", "lastModified": 1543857829148}]}} |
| QUERY PLAN |
|---|
| Nested Loop (cost=0.02..1.55 rows=1 width=64) (actual time=0.022..0.024 rows=1 loops=1) |
| CTE data |
| -> Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.001 rows=1 loops=1) |
| -> CTE Scan on data d (cost=0.00..0.02 rows=1 width=64) (actual time=0.002..0.003 rows=1 loops=1) |
| -> Function Scan on jsonb_array_elements (cost=0.01..1.51 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1) |
| Filter: ((value ->> 'key'::text) = 'DAILY_LIMITS'::text) |
| Rows Removed by Filter: 1 |
| Planning time: 0.073 ms |
| Execution time: 0.407 ms |
| somedata | data |
|---|---|
| somedata | {"Limits":{"fields":[{"key":"DAILY_LIMITS","value":"1559","lastModified":1543857829148},{"key":"MONTHLY_LIMIT","value":"25590","lastModified":1543857829148}]}} |
| QUERY PLAN |
|---|
| CTE Scan on data d (cost=0.01..0.03 rows=1 width=64) (actual time=0.005..0.005 rows=1 loops=1) |
| Filter: (data ~~ '%DAILY_LIMITS%'::text) |
| CTE data |
| -> Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=1) |
| Planning time: 0.047 ms |
| Execution time: 0.022 ms |