Re: Moving postgresql.conf tunables into 2003...

[jumping in late due to vacation]

On Thu, 3 Jul 2003 17:06:46 -0700, Sean Chittenden
<sean(at)chittenden(dot)org> wrote:
>> is some other problem that needs to be solved. (I'd wonder about
>> index correlation myself; we know that that equation is pretty
>> bogus.)
>
>Could be. I had him create a multi-column index on the date and a
>non-unique highly redundant id.

Tom has already suspected index correlation to be a possible source of
the problem and recommended to CLUSTER on the index. A weakness of
the current planner implementation is that a multi column index is
always thought to have low correlation. In your case even after
CLUSTER the 2-column index on (date, sensorid) is treated like a
single column index with correlation 0.5.

I have an experimental patch lying around somewhere that tries to work
around these problems by offering different estimation methods for
index scans. If you are interested, I'll dig it out.

In the meantime have him try with a single column index on date.

On 04 Jul 2003 08:29:04 -0400, Rod Taylor <rbt(at)rbt(dot)ca> wrote:
|That's one heck of a poor estimate for the number of rows returned.
|
|> -> Seq Scan on mss_fwevent (cost=0.00..223312.60 rows=168478 width=12)
| (actual time=24253.66..24319.87 rows=320 loops=1)

> -> Index Scan using mss_fwevent_evtime_sensorid_idx on mss_fwevent
> (cost=0.00..2442524.70 rows=168478 width=12)
> (actual time=68.36..132.84 rows=320 loops=1)
> Index Cond: ((evtime > (now() - '06:00'::interval)) AND (sensorid = 7))
> Filter: (NOT "action")

Estimated number of rows being wrong by a factor 500 seems to be the
main problem hiding everything else. With statistics already set to
1000, does this mean that sensorid, evtime, and action are not
independent? It'd be interesting to know whether the estimation error
comes from "Index Cond" or from "Filter".

Servus
Manfred