Re: Speedup patch for getTables() and getIndexInfo()

Panu,

The getIndexInfo uses an outer join which won't be backward compatible?
I did apply the getTables patch and will commit soon, can you modify the
getIndexInfo part?

Dave
On Sun, 2002-09-08 at 14:18, Panu Outinen wrote:
> Hi !!
> 
> The following patch speeds up the queries in getTables() and 
> getIndexInfo(). It replaces the looping of queries with a simple join 
> operation. Patch is against the current CVS (2002-09-08).
> 
>    - Panu
> 
> PS. Tested against PostgreSQL 7.1.2 and PostgreSQL 7.2.1 databases
> 
> --------------------------------------------------------------------------
> 
> --- AbstractJdbc1DatabaseMetaData.java.org	Sun Sep  8 20:58:57 2002
> +++ AbstractJdbc1DatabaseMetaData.java	Sun Sep  8 21:02:58 2002
> @@ -1836,7 +1836,9 @@
>   		f[4] = new Field(connection, "REMARKS", iVarcharOid, NAME_SIZE);
> 
>   		// Now form the query
> -		StringBuffer sql = new StringBuffer("select relname,oid,relkind from 
> pg_class where (");
> +		StringBuffer sql = new 
> StringBuffer(connection.haveMinimumServerVersion("7.2") ?
> +		  "select relname,oid,relkind,obj_description(oid,'pg_class') from 
> pg_class where (" :
> +    		  "select c.relname,c.oid,c.relkind,d.description from pg_class c 
> left outer join pg_description d on (d.objoid = c.oid) where (" );
> 
>   		boolean notFirst = false;
>   		for (int i = 0;i < types.length;i++)
> @@ -1863,24 +1865,6 @@
>   		while (r.next())
>   		{
>   			byte[][] tuple = new byte[5][0];
> -
> -			// Fetch the description for the table (if any)
> -			String getDescriptionStatement =
> -				connection.haveMinimumServerVersion("7.2") ?
> -				"select obj_description(" + r.getInt(2) + ",'pg_class')" :
> -				"select description from pg_description where objoid=" + r.getInt(2);
> -
> -			java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
> -
> -			byte remarks[] = null;
> -
> -			if (((AbstractJdbc1ResultSet)dr).getTupleCount() == 1)
> -			{
> -				dr.next();
> -				remarks = dr.getBytes(1);
> -			}
> -			dr.close();
> -
>   			String relKind;
>   			switch (r.getBytes(3)[0])
>   			{
> @@ -1911,7 +1895,7 @@
>   			tuple[1] = null;		// Schema name
>   			tuple[2] = r.getBytes(1);	// Table name
>   			tuple[3] = (relKind == null) ? null : relKind.getBytes();	// Table type
> -			tuple[4] = remarks;		// Remarks
> +			tuple[4] = r.getBytes(4);	//Remarks
>   			v.addElement(tuple);
>   		}
>   		r.close();
> @@ -3096,8 +3080,8 @@
>   							   "x.indkey, " +
>   							   "c.reltuples, " +
>   							   "c.relpages, " +
> -							   "x.indexrelid " +
> -							   "FROM pg_index x, pg_class c, pg_class i, pg_am a " +
> +							   "b.attname " +
> +							   "FROM pg_index x left outer join pg_attribute b on (x.indexrelid 
> = b.attrelid), pg_class c, pg_class i, pg_am a " +
>   							   "WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
>   							   " AND (c.oid = x.indrelid) " +
>   							   " AND (i.oid = x.indexrelid) " +
> @@ -3118,7 +3102,6 @@
>   			{
>   				columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
>   			}
> -			java.sql.ResultSet columnNameRS = connection.ExecSQL("select a.attname 
> FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
>   			for (int i = 0; i < columnOrdinals.length; i++)
>   			{
>   				byte [] [] tuple = new byte [13] [];
> @@ -3134,14 +3117,7 @@
>   						   Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes() 
> :
>   						   Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
>   				tuple[7] = Integer.toString(i + 1).getBytes();
> -				if (columnNameRS.next())
> -				{
> -					tuple[8] = columnNameRS.getBytes(1);
> -				}
> -				else
> -				{
> -					tuple[8] = "".getBytes();
> -				}
> +				tuple[8] = r.getBytes(9);
>   				tuple[9] = null;  // sort sequence ???
>   				tuple[10] = r.getBytes(7);	// inexact
>   				tuple[11] = r.getBytes(8);
> 
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 
>