Re: LIKE optimization - Mailing list pgsql-novice

Tom Lane wrote:
>
> > BTW: Should all cols that appear in where clauses be indexed (in
> > general)?
>
> Not necessarily, although in this case it's a good idea to have the
> index on hotels.user_id.
>
> I think what you need is a VACUUM ANALYZE on hotels.  The planner seems
> not to realize that user_id is a unique key (at least I assume it is
> from the reference to hotels_pkey).


=> VACUUM ANALYZE hotels; (I also did a "VACUUM ANALYZE;")
VACUUM
=> explain select h.user_id as hotel_id, h.m1_sterne as sterne,
h.m1_hotel as hotel, h.m1_ort as ort, h.m1_plz as plz, h.m1_region as
region, sum(k.preis * 1) as preis from hotels h, best_EZ k
where
  h.user_id = k.hotel_id and
  h.m1_region like 'Deutschland %' and
  h.m1_plz like '%' and
  h.m1_ort like '%' and
  h.m1_sterne like '%'
  k.datum between '2001-02-01'::date and '2001-02-15'::date - 1 and
  k.datum>now() and
  k.menge - k.reserviert - k.gebucht>=1 and
group by h.user_id, h.m1_hotel, h.m1_sterne, h.m1_ort, h.m1_plz,
h.m1_region, h.user_id having count(*)>=14;
NOTICE:  QUERY PLAN:

Aggregate  (cost=910.31..910.33 rows=0 width=92)
  ->  Group  (cost=910.31..910.33 rows=1 width=92)
        ->  Sort  (cost=910.31..910.31 rows=1 width=92)
              ->  Nested Loop  (cost=0.00..910.30 rows=1 width=92)
                    ->  Seq Scan on hotels h  (cost=0.00..14.84 rows=1
width=72)
                    ->  Seq Scan on best_ez k  (cost=0.00..894.80
rows=53 width=20)
:((((((((((((

Yes, hotels_pkey is the primary key and without LIKE it recognizes it as
such (Index Scan using hotels_pkey on hotels h  (cost=0.00..2.02 rows=1
width=72))
(BTW: In one of the other messages I mailed the relevant tables/keys)

What makes me wonder is:

=> \d best_ez_hotel_id_key
Index "best_ez_hotel_id_key"
 Attribute |     Type
-----------+--------------
 hotel_id  | varchar(200)
 datum     | date
unique btree

best_ez_hotel_id_key is the (unique) key I use in best_ez. But it seems
like it ALWAYS ignores this (Seq Scan on best_ez).
I'm querying:

best_ez.datum between '2001-02-01'::date and '2001-02-15'::date - 1
best_ez.datum>now() and
best_ez.menge - best_ez.reserviert - best_ez.gebucht >= 1 and

Are indices ignored If there are other criteria on
non-indexed-attributes on the same table ?

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