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