Re: Unique indexes not unique?

On Mon, 13 Jan 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mkel 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 statistics
> > 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 = 1 or a = 2) and b =
> 42 and an index on (a,b), include the clause b = 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 = 'svt' OR usr = 'svt1' OR
usr = 'svt2')
AND modified >= 1042239600::bigint AND modified < 1042498800::bigint AND
category != '' AND (flags & 16) > 0 AND title != '<a25uniq>'
ORDER BY modified DESC LIMIT 1000;

returns

Limit (cost=607870.16..607870.16 rows=94 width=372)
-> Sort (cost=607870.16..607870.16 rows=95 width=372)
-> Index Scan using agentresults2_usr, agentresults2_usr,
agentresults2_usr on agentresults (cost=0.00..607867.04 rows=95
width=372)

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

returns

Limit (cost=22669.68..22669.68 rows=95 width=372)
-> Sort (cost=22669.68..22669.68 rows=96 width=372)
-> Index Scan using agentresults2_modified_user,
agentresults2_modified_user, agentresults2_modified_user on agentresults
(cost=0.00..22666.52 rows=96 width=372)

--