Re: text equality worse than pattern matching (v8.1.8) - Mailing list pgsql-performance

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]