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

At 10:00 12.9.2002 -0400, Dave Cramer wrote:
>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?

Well, both getTables() and getIndexInfo() part of my patch need the 'outer
join' functionality. And e.g. getColumns() uses 'outer join' always in
current code!

So how far backward compatible does it need to be nowadays ? I mean 7.3
version of Postgresql is already coming, isn't 7.1.x enough ?

   - Panu



>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
> >
> >
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org