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 similar times between btree and gin for indexes on "category"
columns (ca 20 to 200 distinct values)
For me, gin clearly wins 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 overhead remind small :)
best regards,
Marc Mamin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
