Re: general PG network slowness (possible cure) (repost)

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