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.