Re: [HACKERS] GIN - Generalized Inverted iNdex. Try 2.
On Wed, 26 Apr 2006, Teodor Sigaev wrote: We (me and Oleg) are glad to present GIN to PostgreSQL. If community will agree, we will commit it to HEAD branch. http://www.sigaev.ru/gin/gin.gz http://www.sigaev.ru/gin/README.txt Install: % cd pgsql % zcat gin.gz | patch -p0 make and initdb, install tsearch2 I just built this, and noticed that the regression test for opr_sanity fails with your patch. I attached the regression.diffs. -- BOFH excuse #85: Windows 95 undocumented feature *** ./expected/opr_sanity.out Wed Jan 25 18:35:51 2006 --- ./results/opr_sanity.outWed Apr 26 08:31:13 2006 *** *** 778,785 WHERE p4.amopclaid = p2.oid AND p4.amopsubtype = p3.amopsubtype); oid | amname | oid | opcname | amopsubtype ! -++-+-+- ! (0 rows) -- Check that amopopr points at a reasonable-looking operator, ie a binary -- operator yielding boolean. --- 778,791 WHERE p4.amopclaid = p2.oid AND p4.amopsubtype = p3.amopsubtype); oid | amname | oid | opcname | amopsubtype ! --++--+---+- ! 2742 | gin| 2745 | _int4_ops | 0 ! 2742 | gin| 2745 | _int4_ops | 0 ! 2742 | gin| 2745 | _int4_ops | 0 ! 2742 | gin| 2746 | _text_ops | 0 ! 2742 | gin| 2746 | _text_ops | 0 ! 2742 | gin| 2746 | _text_ops | 0 ! (6 rows) -- Check that amopopr points at a reasonable-looking operator, ie a binary -- operator yielding boolean. *** *** 825,831 783 | 10 | | 783 | 11 | | 783 | 12 | | ! (24 rows) -- Check that all operators linked to by opclass entries have selectivity -- estimators. This is not absolutely required, but it seems a reasonable --- 831,840 783 | 10 | | 783 | 11 | | 783 | 12 | | ! 2742 |1 | ! 2742 |2 | @ ! 2742 |3 | ~ ! (27 rows) -- Check that all operators linked to by opclass entries have selectivity -- estimators. This is not absolutely required, but it seems a reasonable *** *** 847,854 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND NOT binary_coercible(p3.opcintype, p2.oprleft); amopclaid | amopopr | oid | oprname | opcname ! ---+-+-+-+- ! (0 rows) SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 --- 856,869 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND NOT binary_coercible(p3.opcintype, p2.oprleft); amopclaid | amopopr | oid | oprname | opcname ! ---+-+--+-+--- ! 2746 |2750 | 2750 | | _text_ops ! 2745 |2750 | 2750 | | _int4_ops ! 2746 |2751 | 2751 | @ | _text_ops ! 2745 |2751 | 2751 | @ | _int4_ops ! 2746 |2752 | 2752 | ~ | _text_ops ! 2745 |2752 | 2752 | ~ | _int4_ops ! (6 rows) SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 == ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] GIN - Generalized Inverted iNdex. Try 2.
I just built this, and noticed that the regression test for opr_sanity fails with your patch. I attached the regression.diffs. Sorry, this part isn't done yet, because we are waiting of community decision.. We don't add regression test yet. If community don't like to include GIN in core, we make a contrib/gin, but in this case GIN can't use WAL feature because of WAL interface can't call user-defined function. The reason for first diff is a hardcoded 'gist' index: -- We have to exclude GiST, unfortunately, since it hasn't got any fixed -- requirements about strategy operators. SELECT p1.oid, p1.amname, p2.oid, p2.opcname, p3.amopsubtype FROM pg_am AS p1, pg_opclass AS p2, pg_amop AS p3 WHERE p2.opcamid = p1.oid AND p3.amopclaid = p2.oid AND p1.amname != 'gist' AND p1.amstrategies != (SELECT count(*) FROM pg_amop AS p4 WHERE p4.amopclaid = p2.oid AND p4.amopsubtype = p3.amopsubtype); Second is right diff. For the thread one reason is that operations , ~, @ defined for anyarray, but used for particular types. *** ./expected/opr_sanity.out Wed Jan 25 18:35:51 2006 --- ./results/opr_sanity.outWed Apr 26 08:31:13 2006 *** *** 778,785 WHERE p4.amopclaid = p2.oid AND p4.amopsubtype = p3.amopsubtype); oid | amname | oid | opcname | amopsubtype ! -++-+-+- ! (0 rows) -- Check that amopopr points at a reasonable-looking operator, ie a binary -- operator yielding boolean. --- 778,791 WHERE p4.amopclaid = p2.oid AND p4.amopsubtype = p3.amopsubtype); oid | amname | oid | opcname | amopsubtype ! --++--+---+- ! 2742 | gin| 2745 | _int4_ops | 0 ! 2742 | gin| 2745 | _int4_ops | 0 ! 2742 | gin| 2745 | _int4_ops | 0 ! 2742 | gin| 2746 | _text_ops | 0 ! 2742 | gin| 2746 | _text_ops | 0 ! 2742 | gin| 2746 | _text_ops | 0 ! (6 rows) -- Check that amopopr points at a reasonable-looking operator, ie a binary -- operator yielding boolean. *** *** 825,831 783 | 10 | | 783 | 11 | | 783 | 12 | | ! (24 rows) -- Check that all operators linked to by opclass entries have selectivity -- estimators. This is not absolutely required, but it seems a reasonable --- 831,840 783 | 10 | | 783 | 11 | | 783 | 12 | | ! 2742 |1 | ! 2742 |2 | @ ! 2742 |3 | ~ ! (27 rows) -- Check that all operators linked to by opclass entries have selectivity -- estimators. This is not absolutely required, but it seems a reasonable *** *** 847,854 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND NOT binary_coercible(p3.opcintype, p2.oprleft); amopclaid | amopopr | oid | oprname | opcname ! ---+-+-+-+- ! (0 rows) SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 --- 856,869 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND NOT binary_coercible(p3.opcintype, p2.oprleft); amopclaid | amopopr | oid | oprname | opcname ! ---+-+--+-+--- ! 2746 |2750 | 2750 | | _text_ops ! 2745 |2750 | 2750 | | _int4_ops ! 2746 |2751 | 2751 | @ | _text_ops ! 2745 |2751 | 2751 | @ | _int4_ops ! 2746 |2752 | 2752 | ~ | _text_ops ! 2745 |2752 | 2752 | ~ | _int4_ops ! (6 rows) SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 == ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] GIN - Generalized Inverted iNdex. Try 2.
What changed between Try 1 and Try 2? Teodor Sigaev wrote: We (me and Oleg) are glad to present GIN to PostgreSQL. If community will agree, we will commit it to HEAD branch. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] GIN - Generalized Inverted iNdex. Try 2.
Oh I can't read - ignore me :) Teodor Sigaev wrote: Changes from previous patch: * add support for tsearch2 * add 'fuzzy' limit * fixes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
