I'm running in some weird (IMHO) bahviour.
When I search a table for certain text (equality est on the relelvant field)
it takes much more time than doing the same test by adding a trailing '%' and
using the LIKE operator.
With much more I mean 1000+ times slower.
This is the table (sorry for the Italian strings):
----| PSQL |----
noa=# \d ts_t_records
Tabella "public.ts_t_records"
Colonna | Tipo |
Modificatori
---------------+--------------------------+----------------------------------------------------------------------
fiel_uniqueid | bigint | not null
item_uniqueid | bigint | not null
reco_alphanum | text | not null default ''::text
reco_floating | double precision | default 0.0
reco_integral | bigint | default 0
reco_timedate | timestamp with time zone | default now()
reco_isactive | boolean | default true
reco_effectiv | timestamp with time zone | default '-infinity'::timestamp
with time zone
reco_uniqueid | bigint | not null default
nextval('ts_t_records_reco_uniqueid_seq'::regclass)
Indici:
"ts_i_records_0" btree (item_uniqueid)
"ts_i_records_1" btree (reco_uniqueid)
"ts_i_records_2" btree (reco_isactive, reco_effectiv)
"ts_i_records_3" btree (reco_alphanum)
"ts_i_records_4" btree (fiel_uniqueid)
----| /PSQL |----
And these are the EXPLAINs for the queries:
----| PSQL |----
noa=# EXPLAIN SELECT * FROM ts_t_records WHERE fiel_uniqueid=2 AND
reco_alphanum='TEST' AND reco_isactive AND reco_effectiv<=NOW();
QUERY PLAN
------------------------------------------------------------------------------------------
Bitmap Heap Scan on ts_t_records (cost=5110.50..6191.86 rows=277 width=65)
Recheck Cond: ((reco_alphanum = 'TEST'::text) AND (fiel_uniqueid = 2))
Filter: (reco_isactive AND (reco_effectiv <= now()))
-> BitmapAnd (cost=5110.50..5110.50 rows=277 width=0)
-> Bitmap Index Scan on ts_i_records_3 (cost=0.00..36.32 rows=5234
width=0)
Index Cond: (reco_alphanum = 'TEST'::text)
-> Bitmap Index Scan on ts_irecords_4 (cost=0.00..5073.93
rows=812550 width=0)
Index Cond: (fiel_uniqueid = 2)
(8 righe)
noa=# EXPLAIN SELECT * FROM ts_t_records WHERE fiel_uniqueid=2 AND
reco_alphanum LIKE 'TEST%' AND reco_isactive AND reco_effectiv<=NOW();
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Scan using ts_i_records_3 on ts_t_records (cost=0.00..6.01 rows=1
width=65)
Index Cond: ((reco_alphanum >= 'TEST'::text) AND (reco_alphanum
< 'TESU'::text))
Filter: ((fiel_uniqueid = 2) AND (reco_alphanum ~~ 'TEST%'::text) AND
reco_isactive AND (reco_effectiv <= now()))
(3 righe)
----| /PSQL |----
Not only are query plans very different, but the equality query is much worse
than the pattern matching one.
In my (maybe wrong) mind I expected the reverse.
What's wrong with the my expectations? Am I missing something?
MTIA.
--
Vincenzo Romano
----
Maybe Computers will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1987]
