> I use a bigger psql-table to store information and keep an id-value of
how big ?
> each row in memory of my application for faster access.
related to the previous question : are you sure there won't be a day
where it won't fit ?
> My applications is able to calculate a list of needed id's in very short
> time and then wants to retrieve all rows corresponding to this id's.
> select field1,field2,field3 from mytable where id=XX;
Good for one id, see below
> There is a index on the id-field and the id-field is of type OID, so
> everything should be quite fast. Unfortunately it is not.
Why not use integer (serial) instead of oid ?
> On a 2GHz-machine with 1GB RAM it takes approx. 10seconds to fetch 10000
> rows. In this testscenario I only fetch the OID and no other col.
I think this is pretty fast. The machine runs 1K queries/s, including
generating the query, passing it to postgres via a socket, parsing it,
executing it, returning one row via a socket, etc. If you want faster
results you'll have to get all your results in one query, and only then
can get it a lot faster (not mentioning reducing your server load by a lot
!)
> I dont understand this. Am I expecting far to much? Is 10seconds for the
> retrieval of 10000 OIDs a fine value? I want it to be less than one
For 10K queries, it's fast !
> I also tried to use the IN-operator, which is much more slower. Is there
That's what I'd advise you to use. You should find why it's slow and make
it fast. Why not post the EXPLAIN ANALYZE results for a SELECT * FROM
thetable WHERE id_artikel IN (1000 values) for instance ? WHat plan does
it choose ?
If all else fails, you can create a set-returning function which will
take an array of id's as its parameter, loop on it, do a SELECT for each
oid, and RETURN NEXT for each result ; then you can process the whole
result set in one query ; but it'll be slower than a propermy optimized IN
query...