Re: Large Object problems (was Re: JDBC int8 hack)

Sorry, meant to hit all of these.

On Tue, Apr 10, 2001 at 02:24:24PM +0100, Peter Mount wrote:
> >I'm going to start digging around in the optimizer code so such hacks as
> >mine aren't needed. It's really haenous to find out your production
> >server is freaking out and doing sequential scans for EVERYTHING.
>
> Are you talking about the optimiser in the backend as there isn't one in
> the jdbc driver.

Yeah, in the backend. My patch to the JDBC driver only helps people using
JDBC to get to a database (obviously). From any other access method, a
statement like:

SELECT * FROM Foo where bar=1234

will do a sequential scan even if there is an index Foo_bar_idx on "bar"
if bar is INT8. It seems to me that the optimizer should be able to
notice the index Foo_bar_idx and convert the argument "1234" to an INT8 in
order to use Foo_bar_idx over doing a sequential scan (in which case,
"1234" probably gets converted to INT8 anyhow to do comparisons).

Granted, I'm theorizing. I should probably shut up and RTFS.

Anyhow, all my patch did was tack the "::int8" cast onto parameters that
were set by PreparedStatement.setLong(). We did this after finding that
EXPLAIN'ing this:

SELECT * FROM Foo where bar=1234::int8

didn't degrade to a sequential scan like the other SELECT statement (w/o
the cast).

> >Another hack I need to work on (or someone else can) is to squish in a
> >layer of filesystem hashing for large objects. We tried to use large
> >objects and got destroyed. 40,000 rows and the server barely functioned.
> >I think this is because of 2 things:
> >
> >1) Filehandles not being closed. This was an oversite I've seen covered
> >in the list archives somewhere.
>
> Ok, ensure you are closing the large objects within JDBC. If you are then
> this is a backend problem.
>
> One thing to try is to commit the transaction a bit more often (if you are
> running within a single transaction for all 40k objects). Committing the
> transaction will force the backend to close all open large objects on that
> connection.

We were using setBytes(), as we were trying to minimize porting work from
the previous database we were using. And we were comitting after every
transaction. We switched to Base64 encoding and storing strings, so we're
in better shape now.

I'm going to write some more test code in my evenings and see if I can get
current PostgreSQL to suck up filehandles. I'll post again if I can put
together a coherent bug report or patch.

> >2) The fact that all objects are stored in a the single data directory.
> >Once you get up to a good number of objects, directory scans really take a
> >long, long time. This slows down any subsequent openings of large
> >objects. Is someone working on this problem? Or have a patch already?
>
> Again not JDBC. Forwarding to the hackers list on this one. The naming
> conventions were changed a lot in 7.1, and it was for more flexability.

Right, cool. I'll check out the new codebase. Thanks.

This is *so* the curse of open source: now I'm going to be using up my
personal time to look for bugs I find at work. Oh well, I didn't need
to sleep anyhow. ;-)

--
Kyle.
"I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy McClure