Possible planner bug?

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Possible planner bug?
Date: 2007-03-16 21:09:40
Message-ID: 1174079380.23455.352.camel@dogma.v10.wvs
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vivek Khera 2007-03-16 21:13:50 Re: Lifecycle of PostgreSQL releases
Previous Message Jonathan Vanasco 2007-03-16 20:16:12 Re: planning issue