Date: Fri, 10 Dec 2004 18:28:38 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Tomas =?iso-8859-1?q?Sk=E4re?= <tomas@nocrew.org>
Cc: pgsql-general@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: Query is not using index when it should
In-Reply-To: <80oeh2tm7x.fsf@junk.nocrew.org>
Message-ID: <20041210182453.G92467@megazone.bigpanda.com>
References: <80oeh2tm7x.fsf@junk.nocrew.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=X-UNKNOWN
Content-Transfer-Encoding: QUOTED-PRINTABLE

On Fri, 10 Dec 2004, Tomas [iso-8859-1] Sk=E4re wrote:

> I have a table that looks like this:
>
>          Table "public.cjm_object"
>   Column   |       Type        | Modifiers
> -----------+-------------------+-----------
>  timestamp | bigint            | not null
>  jobid     | bigint            | not null
>  objectid  | bigint            | not null
>  class     | integer           | not null
>  field     | character varying | not null

In 7.4.x and earlier, you need to cast the value you're comparing to into
a bigint in order to make sure the indexes are used (in your timestamp
case it appears to work because the value doesn't fit in a plain integer).
8.0 should handle this better.

> But when doing a search with objectid, class and field, it doesn't use
> the idx_cjm_object1 index.
> db=3D# explain analyze select * from cjm_object where objectid=3D4534 and=
 class=3D12 and field=3D'paroid';

Using one of
 objectid=3D4534::bigint
 objectid=3D'4534'
 objectid=3DCAST(4534 as bigint)
rather than objectid=3D4534 should make this indexable in 7.4.x.