Re: slow queries on large syslog table

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.