Обсуждение: 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:=20=20=20=20=20=20=20=20

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
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
-------------------------------------------------------------------
 Index Scan using idx_subscriber_504700_txns_y2012m05_account_code on
subscriber_504700_txns_y2012m05 lh  (cost=3D0.00..17.02 rows=3D3 width=3D13=
1)
   Index Cond: (account_code =3D '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