Обсуждение: text equality worse than pattern matching (v8.1.8)
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]
On 3/18/07, Vincenzo Romano <vincenzo.romano@gmail.com> wrote: > And these are the EXPLAINs for the queries: please provide output of "explain analyze" of the queries. otherwise - it is not really useful. depesz
The problem seems due to a weird LOCALES setup: the default DB locale was UTF8
while the tables and the client encodig were LATIN9. The index in the
reco_alphanum field had no special operator class defined.
A complete initdb and a reload of everything with the same locale (LATIN9)
fixed the issue, though I'm not sure whether this is an "expected feature" or
not.
Thanks a lot.
P.S.
I've seen hubert depesz lubaczewski's remark only on the web interface on
nabble.com. The email from the list manager never reached my mailbox!
On Sunday 18 March 2007 10:59 Vincenzo Romano wrote:
> 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]