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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
