Обсуждение: intarray index
Hello, I have a question about IntArray contrib package. The docs says: "...current implementation provides index support for one-dimensional array of int4's - gist__int_ops, suitable for small and medium size of arrays (used on default), and gist__intbig_ops for indexing large arrays (we use superimposed signature with length of 4096 bits to represent sets)..." How many elements within single array is suggested within small/medium/large array ? I have arrays with up to 200 elements - which index I should use ? Thanks. ML
On Mon, 28 Nov 2005, Marek Lewczuk wrote: > Hello, > I have a question about IntArray contrib package. The docs says: > "...current implementation provides index support for one-dimensional array > of > int4's - gist__int_ops, suitable for small and medium size of arrays (used on > default), and gist__intbig_ops for indexing large arrays (we use superimposed > signature with length of 4096 bits to represent sets)..." > > How many elements within single array is suggested within small/medium/large > array ? I have arrays with up to 200 elements - which index I should use ? gist__intbig_ops > > Thanks. > > ML > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
The log was not written I think Sorry for the noise /David David Gagnon wrote: > Hi all, > > I just migrated from 8.0 to 8.1 and one of my stored procecure just > hang when trying to create a temp table > > Here is my call: > > select * from usp_Comptabilite_AgeDeCompteClient('M', null, > '2005-01-28', '1', '1', '0', null) > > If you look below in the log you will see that the function just hang > when creating T_CP1 table. > > Is that a know bug .. this code worked with 8.0 > > Thanks for your help > Best Regards > /David > > > > --- FUNCTION > CREATE OR REPLACE FUNCTION usp_Comptabilite_AgeDeCompteClient(VARCHAR, > VARCHAR, DATE, BOOLEAN, BOOLEAN, INT, VARCHAR) RETURNS refcursor AS $$ > DECLARE > > companyId ALIAS FOR $1; > Compte ALIAS FOR $2; > DateRef ALIAS FOR $3; > > DateType ALIAS FOR $4; > Tri ALIAS FOR $5; > NBJour ALIAS FOR $6; > BORRNUMR ALIAS FOR $7; > DateRef_ DATE; > > ref refcursor; > statement varchar(4000); > temp RECORD; > > BEGIN > > IF (DateRef IS NOT NULL) THEN > DateRef_:=DateRef; > ELSE > DateRef_:=CURRENT_DATE; > END IF; > > > > -- Toutes les ressources présentes (incluant le regroupement comptable) > EXECUTE ' > CREATE TEMP TABLE T_RR ( > RRNUM VARCHAR(10) PRIMARY KEY > ) ON COMMIT DROP'; > > EXECUTE ' > CREATE TEMP TABLE T_CR1 ( > CRNUM INT PRIMARY KEY, > CRYPNUM VARCHAR(10), > CRMONT DECIMAL, > CRDATE DATE, > CRRRNUM VARCHAR(10), > CRACNUM VARCHAR(10), > GLNUM VARCHAR(10), > GLDESC_PRI varchar (100), > GLDESC_SEC varchar (100), > RRRRNUM VARCHAR(10), > RRGROUP INT > ) ON COMMIT DROP'; > > EXECUTE ' > CREATE TEMP TABLE T_CP1 ( > CRNUM INT PRIMARY KEY, > Paye DECIMAL > ) ON COMMIT DROP'; > > EXECUTE ' > CREATE TEMP TABLE T_RA ( > RRNUM VARCHAR(10), > RANUM INTEGER > ) ON COMMIT DROP'; > > EXECUTE ' > CREATE TEMP TABLE T_CR2 ( > CRNUM INT, > CRMONT NUMERIC, > CRDATE DATE, > CRRRNUM VARCHAR(10), > CRACNUM VARCHAR(10), > Solde NUMERIC, > GLNUM VARCHAR(10), > GLDESC_PRI VARCHAR(100), > GLDESC_SEC VARCHAR(100), > RRRRNUM VARCHAR(10), > RRGROUP INT > ) ON COMMIT DROP'; > > > -- Sélection des infos des comptes à recevoir > statement := ' INSERT INTO T_CR1 ( CRNUM, CRYPNUM, CRMONT, CRDATE, > CRRRNUM, CRACNUM, > GLNUM, GLDESC_PRI, GLDESC_SEC, > RRRRNUM, > RRGROUP) > > > > ----LOG----------------- > OG: statement: select * from usp_Comptabilite_AgeDeCompteClient('M', > null, '2005-01-28', '1', '1', '0', null) > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "t_rr_pkey" for table "t_rr" > CONTEXT: SQL statement " > > CREATE TEMP TABLE T_RR ( > > RRNUM VARCHAR(10) PRIMARY KEY > > ) ON COMMIT DROP" > PL/pgSQL function "usp_comptabilite_agedecompteclient" line 28 at > execute statement > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "t_cr1_pkey" for table "t_cr1" > CONTEXT: SQL statement " > > CREATE TEMP TABLE T_CR1 ( > > CRNUM INT PRIMARY KEY, > > CRYPNUM VARCHAR(10), > > CRMONT DECIMAL, > > CRDATE DATE, > > CRRRNUM VARCHAR(10), > > CRACNUM VARCHAR(10), > > GLNUM VARCHAR(10), > > GLDESC_PRI varchar (100), > > GLDESC_SEC varchar (100), > > RRRRNUM VARCHAR(10), > RRGROUP INT > > ) ON COMMIT DROP" > PL/pgSQL function "usp_comptabilite_agedecompteclient" line 33 at > execute statement > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "t_cp1_pkey" for table "t_cp1" > CONTEXT: SQL statement " > > CREATE TEMP TABLE T_CP1 ( > > CRNUM INT PRIMARY KEY, > > Paye DECIMAL > > ) ON COMMIT DROP" > PL/pgSQL function "usp_comptabilite_agedecompteclient" line 48 at > execute statement >
Hi all, I just migrated from 8.0 to 8.1 and one of my stored procecure just hang when trying to create a temp table Here is my call: select * from usp_Comptabilite_AgeDeCompteClient('M', null, '2005-01-28', '1', '1', '0', null) If you look below in the log you will see that the function just hang when creating T_CP1 table. Is that a know bug .. this code worked with 8.0 Thanks for your help Best Regards /David --- FUNCTION CREATE OR REPLACE FUNCTION usp_Comptabilite_AgeDeCompteClient(VARCHAR, VARCHAR, DATE, BOOLEAN, BOOLEAN, INT, VARCHAR) RETURNS refcursor AS $$ DECLARE companyId ALIAS FOR $1; Compte ALIAS FOR $2; DateRef ALIAS FOR $3; DateType ALIAS FOR $4; Tri ALIAS FOR $5; NBJour ALIAS FOR $6; BORRNUMR ALIAS FOR $7; DateRef_ DATE; ref refcursor; statement varchar(4000); temp RECORD; BEGIN IF (DateRef IS NOT NULL) THEN DateRef_:=DateRef; ELSE DateRef_:=CURRENT_DATE; END IF; -- Toutes les ressources présentes (incluant le regroupement comptable) EXECUTE ' CREATE TEMP TABLE T_RR ( RRNUM VARCHAR(10) PRIMARY KEY ) ON COMMIT DROP'; EXECUTE ' CREATE TEMP TABLE T_CR1 ( CRNUM INT PRIMARY KEY, CRYPNUM VARCHAR(10), CRMONT DECIMAL, CRDATE DATE, CRRRNUM VARCHAR(10), CRACNUM VARCHAR(10), GLNUM VARCHAR(10), GLDESC_PRI varchar (100), GLDESC_SEC varchar (100), RRRRNUM VARCHAR(10), RRGROUP INT ) ON COMMIT DROP'; EXECUTE ' CREATE TEMP TABLE T_CP1 ( CRNUM INT PRIMARY KEY, Paye DECIMAL ) ON COMMIT DROP'; EXECUTE ' CREATE TEMP TABLE T_RA ( RRNUM VARCHAR(10), RANUM INTEGER ) ON COMMIT DROP'; EXECUTE ' CREATE TEMP TABLE T_CR2 ( CRNUM INT, CRMONT NUMERIC, CRDATE DATE, CRRRNUM VARCHAR(10), CRACNUM VARCHAR(10), Solde NUMERIC, GLNUM VARCHAR(10), GLDESC_PRI VARCHAR(100), GLDESC_SEC VARCHAR(100), RRRRNUM VARCHAR(10), RRGROUP INT ) ON COMMIT DROP'; -- Sélection des infos des comptes à recevoir statement := ' INSERT INTO T_CR1 ( CRNUM, CRYPNUM, CRMONT, CRDATE, CRRRNUM, CRACNUM, GLNUM, GLDESC_PRI, GLDESC_SEC, RRRRNUM, RRGROUP) ----LOG----------------- OG: statement: select * from usp_Comptabilite_AgeDeCompteClient('M', null, '2005-01-28', '1', '1', '0', null) NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_rr_pkey" for table "t_rr" CONTEXT: SQL statement " CREATE TEMP TABLE T_RR ( RRNUM VARCHAR(10) PRIMARY KEY ) ON COMMIT DROP" PL/pgSQL function "usp_comptabilite_agedecompteclient" line 28 at execute statement NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_cr1_pkey" for table "t_cr1" CONTEXT: SQL statement " CREATE TEMP TABLE T_CR1 ( CRNUM INT PRIMARY KEY, CRYPNUM VARCHAR(10), CRMONT DECIMAL, CRDATE DATE, CRRRNUM VARCHAR(10), CRACNUM VARCHAR(10), GLNUM VARCHAR(10), GLDESC_PRI varchar (100), GLDESC_SEC varchar (100), RRRRNUM VARCHAR(10), RRGROUP INT ) ON COMMIT DROP" PL/pgSQL function "usp_comptabilite_agedecompteclient" line 33 at execute statement NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_cp1_pkey" for table "t_cp1" CONTEXT: SQL statement " CREATE TEMP TABLE T_CP1 ( CRNUM INT PRIMARY KEY, Paye DECIMAL ) ON COMMIT DROP" PL/pgSQL function "usp_comptabilite_agedecompteclient" line 48 at execute statement