Обсуждение: Possible planner bug?

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;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on syslog_p  (cost=60404.31..303245.13 rows=1441595
width=180) (actual time=19229.159..64908.742 rows=1403715 loops=1)
   Recheck Cond: (severity = 1)
   ->  BitmapAnd  (cost=60404.31..60404.31 rows=134693 width=0) (actual
time=19102.728..19102.728 rows=0 loops=1)
         ->  Bitmap Index Scan on syslog_p_ts_where_severity_1_idx
(cost=0.00..18783.83 rows=1441595 width=0) (actual time=339.103..339.103
rows=1405315 loops=1)
         ->  Bitmap Index Scan on syslog_p_severity_ts_idx
(cost=0.00..40899.43 rows=1441595 width=0) (actual
time=18659.069..18659.069 rows=1619356 loops=1)
               Index Cond: (severity = 1)
 Total runtime: 67261.257 ms
(7 rows)


Why look at both indexes and AND them together? If the tuple is alive,
and has severity=1, it will be in the partial index. The only reason I
can think is if the table is not VACUUMed frequently enough, then maybe
it would find fewer dead tuples.

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> The planner chooses what looks to me like a very strange plan for this
> query:

Exactly which PG version is this?

            regards, tom lane

On Fri, 2007-03-16 at 21:30 -0400, Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
> > The planner chooses what looks to me like a very strange plan for this
> > query:
>
> Exactly which PG version is this?
>

=> select version();
                                            version
------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC)
3.4.6 [FreeBSD] 20060305

Regards,
    Jeff Davis

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.

            regards, tom lane


Index: indxpath.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v
retrieving revision 1.217
diff -c -r1.217 indxpath.c
*** indxpath.c    17 Mar 2007 00:11:04 -0000    1.217
--- indxpath.c    19 Mar 2007 18:22:23 -0000
***************
*** 787,793 ****

          foreach(cell2, list2)
          {
!             if (lfirst(cell2) == datum1)
                  return true;
          }
      }
--- 787,793 ----

          foreach(cell2, list2)
          {
!             if (equal(lfirst(cell2), datum1))
                  return true;
          }
      }

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

Jeff Davis <pgsql@j-davis.com> writes:
> What about your patch is a crude hack, by the way? At first glance it
> looks like you're using a more correct test.

The surrounding code and comments would need to be changed to reflect
what's going on.

            regards, tom lane

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?
>

The patch you provided appears to work perfectly. Thanks!

Regards,
    Jeff Davis