On Mon, 2007-03-19 at 14:31 -0400, Tom Lane wrote: > Jeff Davis <pgsql@xxxxxxxxxxx> writes: > > I have two indexes defined on "syslog_p": > > > "syslog_p_severity_ts_idx" btree (severity, ts) > > "syslog_p_ts_where_severity_1_idx" btree (ts) WHERE severity = 1 > > > The planner chooses what looks to me like a very strange plan for this > > query: > > => explain analyze select * from syslog where severity=1; > > The attached crude hack makes it not do that, but I wonder whether it > won't prevent use of bitmap AND in some cases where we'd rather it did > use an AND. Want to try it out? > > Possibly a more salient question is why are you bothering with two > indexes defined like that. Now that I look closely, they seem pretty > redundant. > You're right; they are. I was testing this partial index because I was getting a bad plan due to the relationship of the data distribution between "severity" and "ts". Essentially, I'm expiring old data from this table, and tuples with greater severity stick around longer. Running a DELETE to expire tuples with severity=1 generates a bad plan because it sees a large number of tuples with severity=1, and also a large number of tuples where (ts < now()-'3 days'). However, since I just ran the DELETE a minute ago, there are actually very few such tuples; the tuples older than 3 days are almost all of a greater severity. My experiment was to see if I could get PostgreSQL to realize this by creating a partial index where severity=1. If it's just a partial index, there are no stats on the data distribution, but I make it a functional partial index, postgres keeps stats on it. However, I don't think it's able to use those stats the way I need it to (I didn't expect it to, but I thought I'd try). Anyway, during this whole process I saw that plan and got confused. And it didn't do it in 8.1, so I thought I'd bring it up on the list. This is probably a textbook case for partitioning based on severity (there are only 8). I may end up doing that after I convince myself I won't lose out in some other way. What about your patch is a crude hack, by the way? At first glance it looks like you're using a more correct test. Regards, Jeff Davis
