Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 59C48476B33
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 10:58:30 -0500 (EST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 1539147695C
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 10:58:29 -0500 (EST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 7F9ACD5F7; Mon, 13 Jan 2003 07:58:33 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 756135C02; Mon, 13 Jan 2003 07:58:33 -0800 (PST)
Date: Mon, 13 Jan 2003 07:58:33 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: =?iso-8859-1?Q?Jimmy_M=E4kel=E4?= <jimmy.makela@agent25.se>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: Unique indexes not unique? 
In-Reply-To: <25149.1042471870@sss.pgh.pa.us>
Message-ID: <20030113075407.A56319-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=X-UNKNOWN
Content-Transfer-Encoding: QUOTED-PRINTABLE
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/170
X-Sequence-Number: 11035

On Mon, 13 Jan 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Mon, 13 Jan 2003, [iso-8859-1] Jimmy M=E4kel=E4 wrote:

> > On my dev (7.4devel) box I see it using the composite index three times,
> > but you haven't given explain output for the two queries or any statist=
ics
> > information so that doesn't say much.
>
> [ checks CVS logs... ]  I believe 7.2 should behave the same; the
> relevant change predated 7.2:
>
> 2001-06-05 13:13  tgl
>
> 	* src/: backend/optimizer/path/allpaths.c,
> 	backend/optimizer/path/indxpath.c, include/optimizer/paths.h,
> 	backend/optimizer/path/orindxpath.c: Improve planning of OR
> 	indexscan plans: for quals like    WHERE (a =3D 1 or a =3D 2) and b =3D
> 	42 and an index on (a,b), include the clause b =3D 42 in the
> 	indexquals generated for each arm of the OR clause.  Essentially
> 	this is an index- driven conversion from CNF to DNF.
> 	Implementation is a bit klugy, but better than not exploiting the
> 	extra quals at all ...

>
> There may be a datatype coercion issue: in the example as quoted,
> '123123123213123' is a bigint constant.  If b is int then that
> comparison wouldn't be considered indexable (and if it's bigint, then
> the other comparison against b wouldn't be indexable without adding
> a cast).

In his actual query (he sent me explain results which include the query)
he uses ::bigint on both constants.

-- Quoting the explain section from his message --
EXPLAIN SELECT * FROM agentresults WHERE (usr =3D 'svt' OR usr =3D 'svt1' OR
usr =3D 'svt2')
AND modified >=3D 1042239600::bigint AND modified < 1042498800::bigint AND
category !=3D '' AND (flags & 16) > 0 AND title !=3D '<a25uniq>'
ORDER BY modified DESC LIMIT 1000;


returns

Limit  (cost=3D607870.16..607870.16 rows=3D94 width=3D372)
  ->  Sort  (cost=3D607870.16..607870.16 rows=3D95 width=3D372)
        ->  Index Scan using agentresults2_usr, agentresults2_usr,
agentresults2_usr on agentresults  (cost=3D0.00..607867.04 rows=3D95
width=3D372)

EXPLAIN SELECT * FROM agentresults WHERE (usr =3D 'svt'
AND modified >=3D 1042239600::bigint AND modified < 1042498800::bigint AND
category !=3D '' AND (flags & 16) > 0 AND title !=3D '<a25uniq>')
OR (usr =3D 'svt1'
AND modified >=3D 1042239600::bigint AND modified < 1042498800::bigint AND
category !=3D '' AND (flags & 16) > 0 AND title !=3D '<a25uniq>')
OR (usr =3D 'svt2'
AND modified >=3D 1042239600::bigint AND modified < 1042498800::bigint AND
category !=3D '' AND (flags & 16) > 0 AND title !=3D '<a25uniq>')
ORDER BY modified DESC LIMIT 1000;

returns

Limit  (cost=3D22669.68..22669.68 rows=3D95 width=3D372)
  ->  Sort  (cost=3D22669.68..22669.68 rows=3D96 width=3D372)
        ->  Index Scan using agentresults2_modified_user,
agentresults2_modified_user, agentresults2_modified_user on agentresults
(cost=3D0.00..22666.52 rows=3D96 width=3D372)

--