Re: slow queries on large syslog table - Mailing list pgsql-general

From Stephan Szabo
Subject Re: slow queries on large syslog table
Date
Msg-id 20011213162859.D5442-100000@megazone23.bigpanda.com
Whole thread Raw
In response to Re: slow queries on large syslog table  ("colm ennis" <colm.ennis@eircom.net>)
List pgsql-general
On Thu, 13 Dec 2001, colm ennis wrote:

> hi all,
>
> thanks for your help, its comforting but also kinda scary to know
> im not the only one whos having trouble!
>
> in response to questions....
>
> as i mentioned before, the syslog_table is currently holds about
> 1.7 million rows and is constantly slowly growing, the hostid_table
> and ciscomdgid_table each hold about 80 rows.
>
> the number of rows returned when i ran the query below :
>     SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN (23,3))
> AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000
> was 19 in all cases, so i guess? the row estimations are woefully
> inaccurate.
>
> i ran a vacuum analyse a few minutes prior to trying these queries.

What does:

select * from pg_statistic,pg_class
 where starelid=pg_class.oid and
  relname='syslog_table';
give you?  (That's the evaluated statistics)

I'm wondering if there's very common values that's throwing off the
estimates.

> im not using the -B option so i guess im using the3 default number/size
> buffers.

Well, you'll definately want to change that :).  I'd suggest going into
your postgresql.conf (in the data directory) and raising shared_buffers
and sort_mem. You'll have to play with the values to find the right point
between postgresql's buffers and the system's, but maybe start in the
low thousands.


pgsql-general by date:

Previous
From: Chris Albertson
Date:
Subject: Re: slow queries on large syslog table
Next
From: Jochem van Dieten
Date:
Subject: Re: slow queries on large syslog table