DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:to:subject:date:user-agent:mime-version:content-type:content-transfer-encoding:content-disposition:message-id:from;
	b=p8mgdx8d3Wt1avXPRYBt2cLFPWpS8o+W6hhWqNLvyhYT9W1YNoweFFosAEadyUs0Lb6o0cuJVGBR6awZJ21tXV9CAevLjZF5UFEC2qvCXYaAvn6MUR/jSxTBO7zdsj8dPoAzoNcyAzTT4D6JlQXdXvmD+KHh4LX9hHkvz4Jflm0=
To: pgsql-performance@postgresql.org
Subject: text equality worse than pattern matching (v8.1.8)
Date: Sun, 18 Mar 2007 10:59:19 +0100
User-Agent: KMail/1.9.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200703181059.19708.Vincenzo.Romano@gmail.com>
From: Vincenzo Romano <vincenzo.romano@gmail.com>

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]