Message-ID: <4656EE15.6030101@archonet.com>
Date: Fri, 25 May 2007 15:09:25 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: ptb@inv.it.uc3m.es
Cc: pgsql-performance@postgresql.org
Subject: Re: general PG network slowness (possible cure) (repost)
References: <200705251402.l4PE2LN01473@inv.it.uc3m.es>
In-Reply-To: <200705251402.l4PE2LN01473@inv.it.uc3m.es>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Peter T. Breuer wrote:
> "Also sprach Richard Huxton:"
>> I'm not sure you really want a full RDBMS. If you only have a single 
>> connection and are making basic key-lookup queries then 90% of 
>> PostgreSQL's code is just getting in your way. Sounds to me like gdbm 
> 
> Yep - I could happily tell it not to try and compile a special lookup
> scheme each time, for example!  (how that?). I could presumably also
> help it by preloading the commands I will run and sending over the 
> params only with a "do a no. 17 now!".

PREPARE/EXECUTE (or the equivalent libpq functions).
Also - if you can have multiple connections to the DB you should be able 
to have several queries running at once.

>> (or one of its alternatives) is a good match for you. Failing that, 
>> sqlite is probably the next lowest-overhead solution.
> 
> Not a bad idea. but PG _will_ be useful when folk come to analyse the
> result of the analyses being done. What is slow is getting the data
> into the database now via simple store, fetch and update.

I'd have an hourly/daily bulk-load running from the simple system into 
PG. If you have to search all the data from your app that's not 
practical of course.

>> Of course, if you want to have multiple clients interacting and 
>> performing complex 19-way joins on gigabyte-sized tables with full-text 
> 
> Well, the dbs are in the tens of MB from a single run over a single
> file (i.e analysis of a single 30KLOC source).  The complete analysis
> space is something like 4000 times that, for 4300 C files in the linux
> kernel source. And then there is all the linux kernel versions. Then
> there is godzilla and apache source ..

If you're doing some sort of token analysis on source-code you probably 
want to look into how tsearch2 / trigram / Gist+GIN indexes work. It 
might be that you're doing work in your app that the DB can handle for you.

>> indexing and full transaction control then you *do* want a RDBMS.
> 
> We want one anyway. The problem is filling the data and the simple
> fetch and update queries on it.

OK

> I really think it would be worthwhile getting some developer to tell me
> where the network send is done in PG.

-- 
   Richard Huxton
   Archonet Ltd