Hi,
does anybody known why this takes so long (> 2 seconds).
update Request set Feedback = r.Erzeugt + q.Alarm2
from Request r, Queue q
where r.Request_Id = 4312 and r.Queue_Id = q.Queue_Id;
There are indices on all relevant columns but Alarm2.
The tables are:
Table = queue
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| queue_id | int4 | 4 |
| name | varchar() | 128 |
| qtag | varchar() | 16 |
| absender | text | var |
| adresse | text | var |
| bearbeiter | varchar() | 16 |
| bearbeiter1 | varchar() | 16 |
| bearbeiter2 | varchar() | 16 |
| revisor | varchar() | 16 |
| revisor1 | varchar() | 16 |
| revisor2 | varchar() | 16 |
| alarm1 | reltime | 4 |
| alarm2 | reltime | 4 |
| sofort | bool | 1 |
| lastmoddate | abstime | 4 |
| lastmoduser | varchar() | 16 |
+----------------------------------+----------------------------------+-------+
indices on: queue_id, revisor, bearbeiter
total entries about 10
Table = request
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| request_id | int4 | 4 |
| erzeugt | abstime | 4 |
| bearbeitet | abstime | 4 |
| bearbeiter | varchar() | 16 |
| beschreibung | text | var |
| status | int4 | 4 |
| stadium | varchar() | 16 |
| level | int4 | 4 |
| locked | bool | 1 |
| qtag | varchar() | 16 |
| queue_id | int4 | 4 |
| priority | varchar() | 16 |
| sender | varchar() | 128 |
| lastmoddate | abstime | 4 |
| lastmoduser | varchar() | 16 |
| erstreaktion | abstime | 4 |
| feedback | abstime | 4 |
+----------------------------------+----------------------------------+-------+
indices on: request_id, queue_Id, Erzeugt, Erstreaktion, Feedback, some more
total entries about 4000
erstreaktion and feedback was added later using 'alter table add column...'
I did my vacuum on both tables recently.
Postgres Version 6.3.2
Any ideas?
\magnus