Обсуждение: Perfomance problems

Hi

    I'm building an app (A) that inserts about 30.000 - 40.000 records in a
table per day. Another app (B) get this records and process it, an then
the records are deleted.  The A app gets 50 records each time, this SQL
statement takes 10-15 seconds. I make a vaccum and the time is now about
5-10 seconds, bu it's to slow. It enqueue records, about 10-20 in each.
The B app takes less than a second in process each message.
    The statement is
    SELECT * FROM table_a
    WHERE number = 123
        AND msg_orig = 'A'
    ORDER by id;

The table is:

    id serial primary key,
    data varchar(260),
    number varchar(12)
    time_a timestamp,
    time_b timestamp,
    msg_orig varchar(3)

Indexes:
    Number,msg_orig, id, time_b

Please, Help, What I'm doing bad?
I'm using 7.1.2 in Solaris.

Thanks.

On 6 Nov 2001, Manuel Duran Aguete wrote:

> Hi
>
>     I'm building an app (A) that inserts about 30.000 - 40.000 records in a
> table per day. Another app (B) get this records and process it, an then
> the records are deleted.  The A app gets 50 records each time, this SQL
> statement takes 10-15 seconds. I make a vaccum and the time is now about
> 5-10 seconds, bu it's to slow. It enqueue records, about 10-20 in each.
> The B app takes less than a second in process each message.

First did you do just a vacuum or a vacuum analyze, and what does explain
show for the query, and do you have any very frequent (out of scale for
the average values) values that aren't null in the data?  Also, are the
indexes a single index for each of those fields or a single multicolumn
index?