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

Re: LIKE optimization

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Nabil Sayegh <nsmail(at)sayegh(dot)de>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: LIKE optimization
Date: 2001-01-17 16:16:20
Message-ID: 17202.979748180@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Nabil Sayegh <nsmail(at)sayegh(dot)de> writes:
> 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).

That index is useless for this query, since you have no restriction
clause on hotel_id, which is the major sort key for the index.  If the
index were on (datum, hotel_id) then it might be profitable to use the
WHERE clauses about datum with the index.

The index could be used for performing the join, if this table were
the inner table, but the planner is deciding to put this table on the
outside and use the other table's index for joining instead.  That's
probably the correct choice given the other restriction clauses.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2001-01-17 17:15:01
Subject: Re: Table recognition
Previous:From: Nabil SayeghDate: 2001-01-17 16:06:28
Subject: Re: LIKE optimization

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