Re: slow queries on large syslog table

Stephan Szabo wrote:

> On Thu, 13 Dec 2001, colm ennis wrote:
>>
>>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.


I would guess that what is returned as the estimates for the amount of
matching rows is the default estimate of 1%. There is a setting
somewhere(?) where you can change the default estimates. It would be
interesting to know what happens to the default query plans if you
change these settings.

Jochem