Thread: BUG #4954: very slow query with 2 statements

The following bug has been logged online:

Bug reference:      4954
Logged by:          Axel Fix
Email address:      axel.fix@sitqsystems.com
PostgreSQL version: 8.3 and 8.4
Operating system:   Windows XP
Description:        very slow query with 2 statements
Details:

Hello,

I have a simple er-model with 3 tables for 2D objects and use the following
statements for query data:
  s1 = "select t1.id,t1.type... from t0,t1
      where t1.id_t0 = t0.id and t0.name='x1000'"
  s2 = "select X,Y from t2 where id_t1=?"

The table t1 returns 1000 elements which I iterate and use the returned id
for the prepared statement s2. This way the server executes s2 1000 times.
This seems to be very slow (>10sec!!! - the time grows with the whole number
of datasets in the tables).

Table t2 contains about 2.500 elements for the 1000 elements of t1.

If I use a single statement, where I get an all in one result set, it will
be finished after <100ms!
The collected statement (with right table and column names) is:
sc = "select t_graphobj.ID,type,fill,color,x,y\n" +
     "  from t_graphdata,t_graphobj,t_names\n" +
     " where ID_GRAPHOBJ=t_graphobj.ID\n" +
     "   and t_graphobj.ID_NAMES=t_names.ID\n" +
     "   and t_names.NAME='x1000'\n" +
     " order by t_graphobj.id"

If you need the java source code and SQL statements for the table, let me
know.

Thanks in advance,

Axel Fix

This is not a bug. Each query requires a round-trip to the server so
it is natural that a lot of queries take additional time.

/M

On Wed, Jul 29, 2009 at 1:01 PM, Axel Fix<axel.fix@sitqsystems.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A04954
> Logged by: =A0 =A0 =A0 =A0 =A0Axel Fix
> Email address: =A0 =A0 =A0axel.fix@sitqsystems.com
> PostgreSQL version: 8.3 and 8.4
> Operating system: =A0 Windows XP
> Description: =A0 =A0 =A0 =A0very slow query with 2 statements
> Details:
>
> Hello,
>
> I have a simple er-model with 3 tables for 2D objects and use the followi=
ng
> statements for query data:
> =A0s1 =3D "select t1.id,t1.type... from t0,t1
> =A0 =A0 =A0where t1.id_t0 =3D t0.id and t0.name=3D'x1000'"
> =A0s2 =3D "select X,Y from t2 where id_t1=3D?"
>
> The table t1 returns 1000 elements which I iterate and use the returned id
> for the prepared statement s2. This way the server executes s2 1000 times.
> This seems to be very slow (>10sec!!! - the time grows with the whole num=
ber
> of datasets in the tables).
>
> Table t2 contains about 2.500 elements for the 1000 elements of t1.
>
> If I use a single statement, where I get an all in one result set, it will
> be finished after <100ms!
> The collected statement (with right table and column names) is:
> sc =3D "select t_graphobj.ID,type,fill,color,x,y\n" +
> =A0 =A0 " =A0from t_graphdata,t_graphobj,t_names\n" +
> =A0 =A0 " where ID_GRAPHOBJ=3Dt_graphobj.ID\n" +
> =A0 =A0 " =A0 and t_graphobj.ID_NAMES=3Dt_names.ID\n" +
> =A0 =A0 " =A0 and t_names.NAME=3D'x1000'\n" +
> =A0 =A0 " order by t_graphobj.id"
>
> If you need the java source code and SQL statements for the table, let me
> know.
>
> Thanks in advance,
>
> Axel Fix
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>