Thread: optimization for repeated values within JOINs

Hello,

Does the PostgreSQL JDBC driver/protocol perform any optimization for
repeated values created by joins?  For example, let's say you have a
query like

select a.id, a.longText, b.id
   from a join b on b.a_id = a.id
   order by a.id;

In the returned result set, the a.longText value will be duplicated for
every b that matches the row in a.

Is it possible for the driver to do some kind of optimization to prevent
pushing the longText value over the network redundantly when it just
repeats the value from the last row in the result set?

Likewise, since String is immutable, could the JDBC driver use the same
String instance rather than instantiating a new String when the value
returned exactly matches the last row?

I guess this isn't about the JDBC driver so much as the underlying
protocol--you'd need some code to indicate "same as last row" for any
given column.

Has anyone else thought about this, or does it just not affect
performance enough to matter?

-- Bill
--
Bill Schneider
Chief Architect

Vecna Technologies
5004 Lehigh Rd., Suite B
College Park, MD 20740
bschneider@vecna.com
t: 301-864-7253 x1140
f: 301-699-3180

Bill Schneider wrote:

> Does the PostgreSQL JDBC driver/protocol perform any optimization for
> repeated values created by joins?

No. The existing protocol is documented at
http://www.postgresql.org/docs/8.0/static/protocol.html

> Has anyone else thought about this, or does it just not affect
> performance enough to matter?

pgsql-hackers is probably the place to discuss it.

> select a.id, a.longText, b.id
>   from a join b on b.a_id = a.id
>   order by a.id;

In this case, you could of course turn it into two separate queries when
you know the characteristics of the query make that a win. It seems like
a lot of work for marginal benefit to try to do some sort of
duplicate-elimination automatically in the protocol.

-O