many similar indexbased selects are extremely slow - Mailing list pgsql-general

psql8:

I use a bigger psql-table to store information and keep an id-value of
each row in memory of my application for faster access.
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.

So in fact I perform a lot of operations like:

select field1,field2,field3 from mytable where id=XX;

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.

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 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
second and from my experience with postgres this operation is extremely
slow compared to the impressive speed of most other operations.

I also tried to use the IN-operator, which is much more slower. Is there
any other way to speed up things? I can order the list of id's to
retrieve in my application if there is a way to tell psql not to search
the whole index every time but somehow "do better".

If it is of any interest, here is the table. The id is stored as id_artikel.


                                        Table "public.artikelindex"
         Column         |            Type             |
     Modifiers
-----------------------+-----------------------------+----------------------------------------------------
  autor_artikel         | text                        |
  titel_artikel         | text                        |
  jahrgang_zeitschrift  | integer                     |
  jahr_zeitschrift      | character varying(20)       |
  heftnummer            | character varying(30)       |
  seitenzahl_artikel    | character varying(30)       |
  bemerkungen_artikel   | text                        |
  deskriptoren_alt      | text                        |
  deskriptoren_neu      | text                        |
  personennamen_artikel | text                        |
  orte_artikel          | text                        |
  id_artikel            | oid                         |
  id_titel              | oid                         |
  cdate                 | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
  udate                 | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
  uid                   | oid                         |
  gid                   | oid                         |
  mod                   | boolean                     |
Indexes:
     "id_artikel_idx" btree (id_artikel)
     "id_titel_idx" btree (id_titel)
     "idx_artikelindeax_autor" btree (autor_artikel)
     "idx_artikelindex_fingerprint" btree (id_artikel)
     "idx_artikelindex_jahr" btree (jahrgang_zeitschrift)
     "idx_artikelindex_jahrgang" btree (jahr_zeitschrift)
     "idx_artikelindex_zeitschrift" btree (id_titel)
Rules:
     delete_val AS
     ON DELETE TO artikelindex DO  UPDATE counter SET val = counter.val + 1
   WHERE counter.tab::text = 'artikelindex'::character varying::text
     insert_val AS
     ON INSERT TO artikelindex DO  UPDATE counter SET val = counter.val + 1
   WHERE counter.tab::text = 'artikelindex'::character varying::text
     update_val AS
     ON UPDATE TO artikelindex DO  UPDATE counter SET val = counter.val + 1
   WHERE counter.tab::text = 'artikelindex'::character varying::text


And more: here is my retrieving program. I use perl and the DBI-module
and the following code-snip

--------------------------
my $sth=$dbh->prepare(
        'select OID from artikelindex where id_artikel=?');

foreach (@id) {
   my $ret=$sth->execute($_);
   my $x=$sth->fetchrow_arrayref;
}
-------------------------



thnx a lot for any idea,
peter



--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
pilsl@goldfisch.at