APPENDIX · P8 · POSTGRESQL · FOR SLIDES 4 AND 19
← Back to Slide 19
P8. Catalog in JSONB: CHECK and GIN
SELECT id, type, stats FROM items;
SELECT id, stats->'damage' AS damage
FROM items WHERE stats @> '{"damage": 45}';
BEGIN;
SET LOCAL enable_seqscan = off; -- только демонстрация
EXPLAIN (COSTS OFF)
SELECT id FROM items WHERE stats @> '{"damage": 45}';
ROLLBACK;
INSERT INTO items VALUES
('potion_bad', 'Кривое зелье', 'potion', 20, '{"damage": 5}');
What the result should be
3 items; sword_dawn | 45
Example Plan: Bitmap Index Scan
on items_stats_gin (after SET LOCAL).
After ROLLBACK, the setting is restored.
ERROR: violates check constraint
"potion_has_heal"; 3 rows saved.
CHECK using jsonb
GIN
Conclusion
A potion must have a "heal_amount" and must not have "damage."
The @> operator can use GIN. For three rows, the optimizer may prefer a Seq Scan.
A flexible structure is also available in PostgreSQL.
This is an argument for Slide 19. “I chose MongoDB because of its flexible structure” does not count: they are comparing operations, workload, and operating costs.