Skip site navigation (1) Skip section navigation (2)

Re: LIKE optimization

From: "rob" <rob(at)cabrion(dot)com>
To: "Nabil Sayegh" <nsmail(at)sayegh(dot)de>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: LIKE optimization
Date: 2001-01-17 01:42:20
Message-ID: 001401c08026$bec44420$4100fd0a@cabrion.org (view raw or flat)
Thread:
Lists: pgsql-novice
your using    table.attrib like '%'    to match everything except nulls
right?

use table.attribute IS NOT NULL instead.

--rob


----- Original Message -----
From: "Nabil Sayegh" <nsmail(at)sayegh(dot)de>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Tuesday, January 16, 2001 11:18 AM
Subject: LIKE optimization


> Hi,
>
> I'm using 7.0.3 and would like to know if there is a way to optimize
> ... where col1 LIKE "%" ...
>
> with LIKE "%"
> 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 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
> h.m1_region like 'Deutschland %' and h.m1_plz like '%' and h.m1_ort like
> '%' and h.m1_sterne like '%' 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;
>
>
> without LIKE "%"
> 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 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
> h.m1_region like 'Deutschland %' 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;
>
>
> Although the two queries should be equivalent the second takes about 4s
> and the first takes almost 2 MINUTES.
> I optimized this query manually by recognizing the special cases in my
> program, but this shouldn't happen.
>
> --
>  Nabil Sayegh
>  GPG-Key available at http://www.sayegh.de
>  (see http://www.gnupg.org for details)
>


In response to

Responses

pgsql-novice by date

Next:From: Peter RodriguezDate: 2001-01-17 02:46:46
Subject: Table access
Previous:From: Tom LaneDate: 2001-01-16 17:51:33
Subject: Re: LIKE optimization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group