Re: LIKE optimization - Mailing list pgsql-novice

Tom Lane wrote:
>
> Nabil Sayegh <nsmail@sayegh.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)