The following bug has been logged on the website:
Bug reference: 11500
Logged by: Marko Tiikkaja
Email address: marko@joh.to
PostgreSQL version: 9.1.12
Operating system: Linux
Description:
Hi,
We've been observing a performance problem where a PRIMARY KEY index is not
being used. The problem looks like this:
pg2=#* explain analyze select * from events where eventid = 132685185 and
processed = 0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_events_processed on events (cost=0.00..7.73 rows=1
width=106) (actual time=31.808..31.808 rows=0 loops=1)
Index Cond: (processed = 0)
Filter: (eventid = 132685185)
Total runtime: 31.852 ms
(4 rows)
pg2=#* explain analyze select * from events where eventid = 132685185 and
processed+0 = 0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using events_pkey on events (cost=0.00..12.38 rows=1 width=106)
(actual time=0.071..0.071 rows=0 loops=1)
Index Cond: (eventid = 132685185)
Filter: ((processed + 0) = 0)
Total runtime: 0.109 ms
(4 rows)
I'm guessing that's happening because the index on processed is smaller
(7GB, relpages=900880 vs 3.7GB, relpages=478225). The statistics say that
there are no rows where processed=0 (and it's not far from the truth), but
it's still a risky plan compared to the PK lookup. The index
index_events_processed is an index on events(processed), which should
probably be a partial index on WHERE processed = 0, but I thought I'd
report this plan anyway.
Any thoughts?
