Обсуждение: \d tablename with psql over slow links...

Hi.

The \d tablename is too slow when its comes to usage over a dialup connection.

What i observed is that psql issues a sperate set of sql for getting default
value of each attribute if it has.  The number sqls increases  as the
size of table increases (no of attributes).

the information abt the default values can also be got by using a subselect
in the top level query which psql does to get list of attributes.

over local/fast links it may not be significant problem , but for dial up
connections it does.

I observed that a table which used to take 16 secs with \d was only taking
6 secs if the query for default attribute values are shifted to top level query in
psql/describe.c  ( function  describeOneTableDetails ) .

below is the diff  for describe.c

Any comments?


[root@subho psql]# diff -B ../safepsql/describe.c describe.c
638a639
>       unsigned int defaultcol = 5;
706a709,714
>
>       /* my code additions 1 */
>       appendPQExpBuffer(&buf, ", (SELECT substring(adsrc for 128)  FROM pg_catalog.pg_attrdef
>       WHERE adrelid =%s AND adnum = a.attnum ) "  , oid  );>       /* ends 1 */
>
>
760,768d775
<                               PGresult   *result;
<
<                               printfPQExpBuffer(&buf,
<                                                                 "SELECT substring(d.adsrc for
128) FROM pg_catalog.pg_attrdef d\n"<                                                          "WHERE d.adrelid = '%s'
ANDd.adnum = %s", 
<                                                                 oid, PQgetvalue(res, i, 4));
<
<                               result = PSQLexec(buf.data, false);
<
772d779
<                               strcat(cells[i * cols + 2], result ? PQgetvalue(result, 0, 0) : "?");
774c781,784
<                               PQclear(result);
---
>                               if (verbose)
>                                       defaultcol = 6;
>                               strcat(cells[i * cols + 2], PQgetvalue(res, i, defaultcol  ) );
>


-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

<mallah@trade-india.com> writes:
> below is the diff  for describe.c

"diff -c" please.  Without context a diff is essentially worthless,
since your version of the input file is unlikely to match development
sources.

            regards, tom lane

here is output of
$ diff -c <original> <modified>

sorry i am not too familiar with diff.
















> <mallah@trade-india.com> writes:
>> below is the diff  for describe.c
>
> "diff -c" please.  Without context a diff is essentially worthless, since your version of the
> input file is unlikely to match development sources.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)--------------------------- TIP 6: Have you
> searched our list archives?
>
> http://archives.postgresql.org







-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

<mallah@trade-india.com> writes:
> I observed that a table which used to take 16 secs with \d was only taking
> 6 secs if the query for default attribute values are shifted to top level query in
> psql/describe.c  ( function  describeOneTableDetails ) .

I've applied this patch with some small improvements (to shortcircuit
the sub-SELECT for columns with no default).

            regards, tom lane

thank you.

On Monday 24 February 2003 09:25 am, Tom Lane wrote:
> <mallah@trade-india.com> writes:
> > I observed that a table which used to take 16 secs with \d was only
> > taking 6 secs if the query for default attribute values are shifted to
> > top level query in psql/describe.c  ( function  describeOneTableDetails )
> > .
>
> I've applied this patch with some small improvements (to shortcircuit
> the sub-SELECT for columns with no default).
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--

Regds
Mallah

----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.