Postgres Pro
Downloads
Home   >   mailing lists

Thread: FW: Optimisation of IN condition

FW: Optimisation of IN condition

From
"Mayers, Philip J"
Date:
I've got some tables:

create table interface (
  machineid text,
  mac macaddr,
  primary key(mac)
);

create table arptable (
  router text,
  interface int2,
  mac macaddr,
  ip inet
);

They're big, 10k rows in interface, maybe 35k in arptable. I want to do
this:

hdb=> explain select * from arptable where mac not in (select mac from
interface);
NOTICE:  QUERY PLAN:

Seq Scan on arptable  (cost=0.00..407762.81 rows=4292 width=48)
  SubPlan
    ->  Seq Scan on interface  (cost=0.00..189.96 rows=8796 width=6)

But, of course, that a very expensive task. Now, it seems to me that, since
I have an index on mac in interface, I *should* in theory be able to speed
this up, in the following pseudo-code fashion:

foreach mac in arptable:
    if lookup(mac,interface_pkey):
        return *

Do you see what I'm getting at? Can I refashion the query somehow to take
advantage of that? The converse operation, finding registered machines:

hdb=> explain select interface.mac from arptable,interface where
interface.mac = arptable.mac;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
  ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292 width=6)
  ->  Index Scan using interface_pkey on interface  (cost=0.00..2.02 rows=1
width=6)

Is, of course, speedy. How can I take advantage of that. EXCEPT doesn't seem
to help:

hdb=> explain select mac from arptable except select interface.mac from
arptable,interface where interface.mac = arptable.mac;
NOTICE:  QUERY PLAN:

Seq Scan on arptable  (cost=0.00..37933516.98 rows=4292 width=6)
  SubPlan
    ->  Materialize  (cost=8838.17..8838.17 rows=4292 width=12)
          ->  Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
                ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292
width=6)
                ->  Index Scan using interface_pkey on interface
(cost=0.00..2.02 rows=1 width=6)


Help!

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support     |
| Centre for Computing Services    |
| Imperial College                 |
+----------------------------------+

RE: FW: Optimisation of IN condition

From
"Mayers, Philip J"
Date:
Ok, after some suggestions from a colleague, I've refactored the query to
use an outer join, like this:

hdb=> select host.ip as registeredip,arptable.ip as
realip,host.mac,arptable.router,arptable.interface from host,arptable where
host.mac = arptable.mac and host.ip = arptable.ip
hdb-> union
hdb-> select NULL as registeredip,arptable.ip as
realip,arptable.mac,arptable.router,arptable.interface from arptable
hdb-> order by router,interface,mac;

  registeredip   |     realip      |        mac        |          router
| interface
-----------------+-----------------+-------------------+--------------------
------+-----------
                 | 192.168.4.39    | 00:10:5a:bd:79:2f |
a-routername.domain.xx   |        21
                 | 192.168.4.238   | 00:10:5a:bd:79:e8 |
a-routername.domain.xx   |        21
 192.168.4.181   | 192.168.4.181   | 00:10:5a:bd:7b:4e |
a-routername.domain.xx   |        21
                 | 192.168.4.181   | 00:10:5a:bd:7b:4e |
a-routername.domain.xx   |        21
                 | 192.168.4.192   | 00:10:5a:bd:7d:35 |
a-routername.domain.xx   |        21
                 | 192.168.4.239   | 00:10:5a:bd:82:6c |
a-routername.domain.xx   |        21
 192.168.4.171   | 192.168.4.171   | 00:10:5a:bd:84:6d |
a-routername.domain.xx   |        21
                 | 192.168.4.171   | 00:10:5a:bd:84:6d |
a-routername.domain.xx   |        21
                 | 192.168.4.212   | 00:10:5a:bd:84:97 |
a-routername.domain.xx   |        21
                 | 192.168.4.194   | 00:10:5a:bd:84:a1 |
a-routername.domain.xx   |        21
 192.168.4.182   | 192.168.4.182   | 00:10:5a:bd:84:c2 |
a-routername.domain.xx   |        21
                 | 192.168.4.182   | 00:10:5a:bd:84:c2 |
a-routername.domain.xx   |        21

Cool - now I can identify unregistered machines using a NULL. But, I'm
getting repeat columns. Do I have to use CORRESPONDING BY (realip,mac),
which postgres doesn't support, or is there another way?

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support     |
| Centre for Computing Services    |
| Imperial College                 |
+----------------------------------+

-----Original Message-----
From: Mayers, Philip J [mailto:p.mayers@ic.ac.uk]
Sent: 08 March 2001 11:22
To: 'pgsql-general@postgresql.org'
Subject: [GENERAL] FW: Optimisation of IN condition



I've got some tables:

create table interface (
  machineid text,
  mac macaddr,
  primary key(mac)
);

create table arptable (
  router text,
  interface int2,
  mac macaddr,
  ip inet
);

They're big, 10k rows in interface, maybe 35k in arptable. I want to do
this:

hdb=> explain select * from arptable where mac not in (select mac from
interface);
NOTICE:  QUERY PLAN:

Seq Scan on arptable  (cost=0.00..407762.81 rows=4292 width=48)
  SubPlan
    ->  Seq Scan on interface  (cost=0.00..189.96 rows=8796 width=6)

But, of course, that a very expensive task. Now, it seems to me that, since
I have an index on mac in interface, I *should* in theory be able to speed
this up, in the following pseudo-code fashion:

foreach mac in arptable:
    if lookup(mac,interface_pkey):
        return *

Do you see what I'm getting at? Can I refashion the query somehow to take
advantage of that? The converse operation, finding registered machines:

hdb=> explain select interface.mac from arptable,interface where
interface.mac = arptable.mac;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
  ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292 width=6)
  ->  Index Scan using interface_pkey on interface  (cost=0.00..2.02 rows=1
width=6)

Is, of course, speedy. How can I take advantage of that. EXCEPT doesn't seem
to help:

hdb=> explain select mac from arptable except select interface.mac from
arptable,interface where interface.mac = arptable.mac;
NOTICE:  QUERY PLAN:

Seq Scan on arptable  (cost=0.00..37933516.98 rows=4292 width=6)
  SubPlan
    ->  Materialize  (cost=8838.17..8838.17 rows=4292 width=12)
          ->  Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
                ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292
width=6)
                ->  Index Scan using interface_pkey on interface
(cost=0.00..2.02 rows=1 width=6)


Help!

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support     |
| Centre for Computing Services    |
| Imperial College                 |
+----------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

RE: FW: Optimisation of IN condition

From
"Mayers, Philip J"
Date:
And a yet-more efficient system, I hope:

select * from arptable where not exists (select 1 from host where
arptable.mac = host.mac) order by router,interface,ip;

Could someone guarantee me that does what I think it does? If so, sorry for
the verbose emails!

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support     |
| Centre for Computing Services    |
| Imperial College                 |
+----------------------------------+

-----Original Message-----
From: Mayers, Philip J [mailto:p.mayers@ic.ac.uk]
Sent: 08 March 2001 12:18
To: 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] FW: Optimisation of IN condition


Ok, after some suggestions from a colleague, I've refactored the query to
use an outer join, like this:

hdb=> select host.ip as registeredip,arptable.ip as
realip,host.mac,arptable.router,arptable.interface from host,arptable where
host.mac = arptable.mac and host.ip = arptable.ip
hdb-> union
hdb-> select NULL as registeredip,arptable.ip as
realip,arptable.mac,arptable.router,arptable.interface from arptable
hdb-> order by router,interface,mac;

  registeredip   |     realip      |        mac        |          router
| interface
-----------------+-----------------+-------------------+--------------------
------+-----------
                 | 192.168.4.39    | 00:10:5a:bd:79:2f |
a-routername.domain.xx   |        21
                 | 192.168.4.238   | 00:10:5a:bd:79:e8 |
a-routername.domain.xx   |        21
 192.168.4.181   | 192.168.4.181   | 00:10:5a:bd:7b:4e |
a-routername.domain.xx   |        21
                 | 192.168.4.181   | 00:10:5a:bd:7b:4e |
a-routername.domain.xx   |        21
                 | 192.168.4.192   | 00:10:5a:bd:7d:35 |
a-routername.domain.xx   |        21
                 | 192.168.4.239   | 00:10:5a:bd:82:6c |
a-routername.domain.xx   |        21
 192.168.4.171   | 192.168.4.171   | 00:10:5a:bd:84:6d |
a-routername.domain.xx   |        21
                 | 192.168.4.171   | 00:10:5a:bd:84:6d |
a-routername.domain.xx   |        21
                 | 192.168.4.212   | 00:10:5a:bd:84:97 |
a-routername.domain.xx   |        21
                 | 192.168.4.194   | 00:10:5a:bd:84:a1 |
a-routername.domain.xx   |        21
 192.168.4.182   | 192.168.4.182   | 00:10:5a:bd:84:c2 |
a-routername.domain.xx   |        21
                 | 192.168.4.182   | 00:10:5a:bd:84:c2 |
a-routername.domain.xx   |        21

Cool - now I can identify unregistered machines using a NULL. But, I'm
getting repeat columns. Do I have to use CORRESPONDING BY (realip,mac),
which postgres doesn't support, or is there another way?

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support     |
| Centre for Computing Services    |
| Imperial College                 |
+----------------------------------+

-----Original Message-----
From: Mayers, Philip J [mailto:p.mayers@ic.ac.uk]
Sent: 08 March 2001 11:22
To: 'pgsql-general@postgresql.org'
Subject: [GENERAL] FW: Optimisation of IN condition



I've got some tables:

create table interface (
  machineid text,
  mac macaddr,
  primary key(mac)
);

create table arptable (
  router text,
  interface int2,
  mac macaddr,
  ip inet
);

They're big, 10k rows in interface, maybe 35k in arptable. I want to do
this:

hdb=> explain select * from arptable where mac not in (select mac from
interface);
NOTICE:  QUERY PLAN:

Seq Scan on arptable  (cost=0.00..407762.81 rows=4292 width=48)
  SubPlan
    ->  Seq Scan on interface  (cost=0.00..189.96 rows=8796 width=6)

But, of course, that a very expensive task. Now, it seems to me that, since
I have an index on mac in interface, I *should* in theory be able to speed
this up, in the following pseudo-code fashion:

foreach mac in arptable:
    if lookup(mac,interface_pkey):
        return *

Do you see what I'm getting at? Can I refashion the query somehow to take
advantage of that? The converse operation, finding registered machines:

hdb=> explain select interface.mac from arptable,interface where
interface.mac = arptable.mac;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
  ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292 width=6)
  ->  Index Scan using interface_pkey on interface  (cost=0.00..2.02 rows=1
width=6)

Is, of course, speedy. How can I take advantage of that. EXCEPT doesn't seem
to help:

hdb=> explain select mac from arptable except select interface.mac from
arptable,interface where interface.mac = arptable.mac;
NOTICE:  QUERY PLAN:

Seq Scan on arptable  (cost=0.00..37933516.98 rows=4292 width=6)
  SubPlan
    ->  Materialize  (cost=8838.17..8838.17 rows=4292 width=12)
          ->  Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
                ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292
width=6)
                ->  Index Scan using interface_pkey on interface
(cost=0.00..2.02 rows=1 width=6)


Help!

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support     |
| Centre for Computing Services    |
| Imperial College                 |
+----------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

length of insert stmt?

From
"chris markiewicz"
Date:
hello

i received an error when someone ran an input stmt with a very long sting.
the field is of type 'text'.  The error (along with the statement) are shown
below.  what is the proper way do execute this insert?

The SQL Statement is too long - INSERT INTO accessor_group ( groupid,
groupname, grouptype, groupclassname, groupdescription, hidden ) VALUES (
22395, 'No TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something Something', 'community',
'com.commnav.sbh.objects.Group', 'No TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer
Make Homer Something SomethingNo TV and No Beer Make Homer Something
SomethingNo TV and No Beer Make Homer Something Somethingv', 'false' )
    at org.postgresql.Connection.ExecSQL(Connection.java:324)
    at org.postgresql.jdbc2.Statement.execute(Statement.java:273)
    at com.commnav.sbh.framework.persist.JDBCEngine.create(JDBCEngine.java:44)
    at
com.commnav.sbh.framework.persist.PersistenceObject.create(PersistenceObject
.java:387)
    at
com.commnav.sbh.applications.group.GroupAddStrategy.process(GroupAddStrategy
.java:87)
    at.................... etc, etc.

Any information/comments would be appreciated.

thanks
chris

Re: length of insert stmt?

From
Gavin Sherry
Date:
Chris,

You seem to have hit the 8Kb row limit. You can fix this by editing
include/config.h and changing BLCKSZ. The maximum is 32Kb.

Note that this is redundant in 7.1

Gavin Sherry
Alcove Systems Engineering.

Re: length of insert stmt?

From
"Richard Huxton"
Date:
From: "chris markiewicz" <cmarkiew@commnav.com>

> hello
>
> i received an error when someone ran an input stmt with a very long sting.
> the field is of type 'text'.  The error (along with the statement) are
shown
> below.  what is the proper way do execute this insert?
>
> The SQL Statement is too long - INSERT INTO accessor_group ( groupid,
> groupname, grouptype, groupclassname, groupdescription, hidden ) VALUES (

[snip >8k of insert]

> Any information/comments would be appreciated.
>
> thanks
> chris

You've hit the infamous 8k limit in Postgres. This applies to database rows
and there is a similar limit to SQL queries. It looks like the SQL limit is
hit here.

You can recompile to increase this up to 32k (see the mailing list archives
for loads on this) or try switching to 7.1 (still in beta) which offers
something called TOAST for storage of large text-fields.

- Richard Huxton

Re: length of insert stmt?

From
Tom Lane
Date:
Gavin Sherry <swm@linuxworld.com.au> writes:
> You seem to have hit the 8Kb row limit.

No, I think he's hit some limit on the size of a query string.  Before
about 7.0, there was a limit on the textual length of queries.  We got
rid of it in the backend and libpq, but I think some of the lesser-used
interface libraries still think they can/should limit query length.

Chris didn't say what version of what interface he was using, though...

            regards, tom lane

RE: length of insert stmt?

From
"chris markiewicz"
Date:
i am using jdbc7.0-1.2...postgres 7.0.2.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 08, 2001 10:28 AM
To: Gavin Sherry
Cc: chris markiewicz; pgsql-general@postgresql.org
Subject: Re: [GENERAL] length of insert stmt?


Gavin Sherry <swm@linuxworld.com.au> writes:
> You seem to have hit the 8Kb row limit.

No, I think he's hit some limit on the size of a query string.  Before
about 7.0, there was a limit on the textual length of queries.  We got
rid of it in the backend and libpq, but I think some of the lesser-used
interface libraries still think they can/should limit query length.

Chris didn't say what version of what interface he was using, though...

            regards, tom lane

Re: length of insert stmt?

From
Tom Lane
Date:
"chris markiewicz" <cmarkiew@commnav.com> writes:
> i am using jdbc7.0-1.2...postgres 7.0.2.

I'm not sure what the current state of play is for query length in JDBC.
It might be fixed in the current 7.1 beta version, or not.  Try asking
over on the pgsql-jdbc list.

As a short-term workaround, you could just look for the relevant
constant in the JDBC source code, and increase it...

            regards, tom lane

Re: length of insert stmt?

From
Barry Lind
Date:
The sql statement length limit was a bug in the jdbc driver that was
fixed in 7.0.3.

--Barry


chris markiewicz wrote:

> i am using jdbc7.0-1.2...postgres 7.0.2.
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, March 08, 2001 10:28 AM
> To: Gavin Sherry
> Cc: chris markiewicz; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] length of insert stmt?
>
>
> Gavin Sherry <swm@linuxworld.com.au> writes:
>
>> You seem to have hit the 8Kb row limit.
>
>
> No, I think he's hit some limit on the size of a query string.  Before
> about 7.0, there was a limit on the textual length of queries.  We got
> rid of it in the backend and libpq, but I think some of the lesser-used
> interface libraries still think they can/should limit query length.
>
> Chris didn't say what version of what interface he was using, though...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Optimisation of IN condition

From
"Rob Arnold"
Date:
select * from arptable where arptable.mac where not exists (select mac from
interface where arptable.mac = interface.mac);

See the chapter in Bruce's book "Subqueries Returning Multiple Columns"

cheers

--rob

----- Original Message -----
From: "Mayers, Philip J" <p.mayers@ic.ac.uk>
To: <pgsql-general@postgresql.org>
Sent: Thursday, March 08, 2001 6:22 AM
Subject: FW: Optimisation of IN condition


>
> I've got some tables:
>
> create table interface (
>   machineid text,
>   mac macaddr,
>   primary key(mac)
> );
>
> create table arptable (
>   router text,
>   interface int2,
>   mac macaddr,
>   ip inet
> );
>
> They're big, 10k rows in interface, maybe 35k in arptable. I want to do
> this:
>
> hdb=> explain select * from arptable where mac not in (select mac from
> interface);
> NOTICE:  QUERY PLAN:
>
> Seq Scan on arptable  (cost=0.00..407762.81 rows=4292 width=48)
>   SubPlan
>     ->  Seq Scan on interface  (cost=0.00..189.96 rows=8796 width=6)
>
> But, of course, that a very expensive task. Now, it seems to me that,
since
> I have an index on mac in interface, I *should* in theory be able to speed
> this up, in the following pseudo-code fashion:
>
> foreach mac in arptable:
>     if lookup(mac,interface_pkey):
>         return *
>
> Do you see what I'm getting at? Can I refashion the query somehow to take
> advantage of that? The converse operation, finding registered machines:
>
> hdb=> explain select interface.mac from arptable,interface where
> interface.mac = arptable.mac;
> NOTICE:  QUERY PLAN:
>
> Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
>   ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292 width=6)
>   ->  Index Scan using interface_pkey on interface  (cost=0.00..2.02
rows=1
> width=6)
>
> Is, of course, speedy. How can I take advantage of that. EXCEPT doesn't
seem
> to help:
>
> hdb=> explain select mac from arptable except select interface.mac from
> arptable,interface where interface.mac = arptable.mac;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on arptable  (cost=0.00..37933516.98 rows=4292 width=6)
>   SubPlan
>     ->  Materialize  (cost=8838.17..8838.17 rows=4292 width=12)
>           ->  Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
>                 ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292
> width=6)
>                 ->  Index Scan using interface_pkey on interface
> (cost=0.00..2.02 rows=1 width=6)
>
>
> Help!
>
> Regards,
> Phil
>
> +----------------------------------+
> | Phil Mayers, Network Support     |
> | Centre for Computing Services    |
> | Imperial College                 |
> +----------------------------------+
>