Re: LIKE optimization
От | Nabil Sayegh |
---|---|
Тема | Re: LIKE optimization |
Дата | |
Msg-id | 3A65C304.929B78F7@sayegh.de обсуждение исходный текст |
Ответ на | LIKE optimization (Nabil Sayegh <nsmail@sayegh.de>) |
Ответы |
Re: LIKE optimization
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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)
В списке pgsql-novice по дате отправления: