Re: slow queries on large syslog table

On Thu, 13 Dec 2001, colm ennis wrote:

> hi antonio,
>
> thanks for your advice.
>
> ive tried a lot of different index combinations, with extremely variable
> results,
> for instance :
> query - SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN
> (23,3)) AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000
> matching messages - 19
>
> with original indexes :
> query time(s) - 225
> explain - Limit (cost=0.00..34559.46 rows=1000 width=24)
> -> Index Scan Backward using syslog_table_stimestamp_index on
> syslog_table (cost=0.00..577149.86 rows=16700 width=24)
>
> with NO! index :
> query time(s) - 77
> explain - Limit (cost=73979.79..73979.79 rows=1000 width=24)
> -> Sort (cost=73979.79..73979.79 rows=16905 width=24)
> -> Seq Scan on syslog_table (cost=0.00..72591.62 rows=16905
> width=24)

Have you been running vacuum analyze? If I'm reading correctly what you
mean, you've got 19 matching messages, but the estimated return rows is
much much larger than that.