From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | colm ennis <colm(dot)ennis(at)eircom(dot)net> |
Cc: | Antonio Fiol Bonnin <fiol(at)w3ping(dot)com>, PostgreSQL General Mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: slow queries on large syslog table |
Date: | 2001-12-14 00:36:51 |
Message-ID: | 20011213162859.D5442-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jochem van Dieten | 2001-12-14 00:38:31 | Re: slow queries on large syslog table |
Previous Message | Chris Albertson | 2001-12-14 00:35:56 | Re: slow queries on large syslog table |