Hello,
First: KUDO !!!
The release notes are extremely promising in regard to performance improvements :-)
I've made some (dirty) tests with BRIN and btree_gin.
(on a smalll Windows laptop ...)
just a few remarks:
- btree_gin deserve a better description than that:
"However, they are useful for GIN testing and as a base for developing other GIN operator classes." I came to
similartimes between btree and gin for indexes on "category" columns (ca 20 to 200 distinct values) For me, gin
clearlywins here thanks to the index size difference. You should really consider moving btree_gin to core ...
- btree_gin on integers doesn't cope well with BETWEEN. Seems to always lead to a full index scan I think I understand
why,but maybe this is worth a comment in the doc to underline the difference to btree.
SELECT * from tgin_1 WHERE cat_id between 1 and 2: http://explain.depesz.com/s/fmqn SELECT * from tgin_1 WHERE cat_id
IN(1,2): http://explain.depesz.com/s/bYg
- BRIN cost: I've made a silly test, where all distinct values exist in all BRIN page ranges: INSERT into tbrin_1
(cat_id,....) SELECT s%20, ... FROM generate_series(1,3000000 )s;CREATE INDEX cat_brin_1 on tbrin_1 using BRIN
(cat_id)with(pages_per_range=64);SELECT * from tbrin_1 WHERE cat_id=10; http://explain.depesz.com/s/9YQR
There seems to be no "fence" against useless BRIN indexes that would allow a fallback on a table scan. But the time
overheadremind small :)
best regards,
Marc Mamin
