On Mon, 2007-03-19 at 14:31 -0400, Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> 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
