Thread: \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/
Attachment
<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.