Thread: Perfomance Problems

Hi
    Sorry , but by error I'd sent this message to postgresql-admin

        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.

--- Manuel Duran Aguete <mdaguete@alsernet.es> wrote:
> 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;

If number is a VARCHAR then why are you selecting it like it is
an INT ("WHERE number = 123" rather than "WHERE number =
'123'")?  You seem to need an index on number.  The index on
time_b does not look like it is necessary.

Brent



__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com