Re: LIKE optimization

Tom Lane wrote:
>
> Nabil Sayegh <nsmail(at)sayegh(dot)de> writes:
> > Although the two queries should be equivalent the second takes about 4s
> > and the first takes almost 2 MINUTES.
>
> It's impossible to say anything intelligent about this with only the
> text of the queries to go on. What indexes do you have on these tables?
> What does EXPLAIN show as the query plan for each query?
>
> Oh, and have you done a VACUUM ANALYZE recently on these tables?
>
> regards, tom lane

I played around with VACUUM once, but I can't remember which database it
was,
because I use several copies of the db for testing.
However, shouldn't "LIKE '%'" be ALWAYS ignored no matter how the
database is structured?

==============================================================================
==============================================================================
=>\d best_ez
Table "best_ez"
Attribute | Type |
Modifier
------------+--------------+--------------------------------------------------
id | integer | not null default
nextval('best_ez_id_seq'::text)
ins_id | text |
hotel_id | varchar(200) | not null default ''
datum | date | not null
corporate | float8 | default '0.0000'
preis | float8 | not null default '0.0000'
menge | integer | not null default '0'
reserviert | integer | default '0'
gebucht | integer | default '0'
Indices: best_ez_hotel_id_key,
best_ez_pkey
==============================================================================
=> \d best_ez_hotel_id_key
Index "best_ez_hotel_id_key"
Attribute | Type
-----------+--------------
hotel_id | varchar(200)
datum | date
unique btree
==============================================================================
=> \d best_ez_pkey
Index "best_ez_pkey"
Attribute | Type
-----------+---------
id | integer
unique btree (primary key)
==============================================================================
==============================================================================
=> \d hotels
Table "hotels"
Attribute | Type | Modifier
--------------------------+-------------+---------------------
m1_hotel | text | default ''
m1_sterne | text | default ''
m1_plz | text | default ''
m1_ort | text | default ''
user_id | text | not null default ''
m1_region | text |
[...]
Index: hotels_pkey
==============================================================================
=> \d hotels_pkey
Index "hotels_pkey"
Attribute | Type
-----------+------
user_id | text
unique btree (primary key)

cu

--
Nabil Sayegh
GPG-Key available at http://www.sayegh.de
(see http://www.gnupg.org for details)