BUG #6719: PG shouldn't filter on same criteria as index search
The following bug has been logged on the website:
Bug reference: 6719
Logged by: BLove
Email address: blove319@hotmail.com
PostgreSQL version: 9.1.3
Operating system: Centos 5
Description:
After doing an equality search on an indexed field, PG is doing a filter on
the same value using a regex. That appears to be a useless operation that
will only consume cpu cycles.
explain select * from transactions.subscriber_504700_txns_y2012m05 lh where
lh.account_code LIKE '34016975\_DAT\_FR\_HF';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_subscriber_504700_txns_y2012m05_account_code on
subscriber_504700_txns_y2012m05 lh (cost=0.00..17.02 rows=3 width=131)
Index Cond: (account_code = '34016975_DAT_FR_HF'::text)
Filter: (account_code ~~ '34016975\_DAT\_FR\_HF'::text)
blove319@hotmail.com writes:
After doing an equality search on an indexed field, PG is doing a filter on
the same value using a regex. That appears to be a useless operation that
will only consume cpu cycles.
Getting rid of the filter expression isn't really worth the trouble.
If we had done the transformation of regexes to index conditions in a
different way, it might be practical to suppress the redundant test,
but given the way the code is structured we can't easily.
regards, tom lane