Re: LIKE optimization

From: Nabil Sayegh <nsmail(at)sayegh(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-novice(at)postgresql(dot)org
Subject: Re: LIKE optimization
Date: 2001-01-17 16:06:28
Message-ID: 3A65C304.929B78F7@sayegh.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2001-01-17 16:16:20 Re: LIKE optimization
Previous Message Tom Lane 2001-01-17 16:05:53 Re: Problems with authentication