On Fri, 10 Dec 2004, Tomas [iso-8859-1] Skäre 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=# explain analyze select * from cjm_object where objectid=4534 and class=12 and field='paroid';
Using one of
objectid=4534::bigint
objectid='4534'
objectid=CAST(4534 as bigint)
rather than objectid=4534 should make this indexable in 7.4.x.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Sat, Dec 11, 2004 at 03:17:13PM +0100, Tomas Skäre wrote:
> select c.* from cjm_object c
> inner join
> (select max(timestamp) as timestamp,objectid,field from cjm_object
> group by objectid,field) t
> using(timestamp,objectid,field)
> where 1=1 and data is not null
> order by objectid,field;
Usually, SELECT max(field) FROM table is better written in PostgreSQL as
SELECT field FROM table ORDER field DESC LIMIT 1.
I don't see the point of "where 1=1", though...
/* Steinar */
--
Homepage: http://www.sesse.net/
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Sat, Dec 11, 2004 at 03:32:13PM +0100, Steinar H. Gunderson wrote:
> On Sat, Dec 11, 2004 at 03:17:13PM +0100, Tomas Skäre wrote:
> > select c.* from cjm_object c
> > inner join
> > (select max(timestamp) as timestamp,objectid,field from cjm_object
> > group by objectid,field) t
> > using(timestamp,objectid,field)
> > where 1=1 and data is not null
> > order by objectid,field;
>
> Usually, SELECT max(field) FROM table is better written in PostgreSQL as
> SELECT field FROM table ORDER field DESC LIMIT 1.
>
> I don't see the point of "where 1=1", though...
I've seen that in generated queries. The generating program uses
"WHERE 1=1" to simplify the addition of other conditions: instead
of checking if it needs to add a WHERE and putting ANDs in the right
places, it simply appends subsequent conditions with " AND condition".
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org