Re: Unique indexes not unique? - Mailing list pgsql-sql

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�kel� 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)       ->
IndexScan 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)       ->
IndexScan using agentresults2_modified_user, 
agentresults2_modified_user, agentresults2_modified_user on agentresults
(cost=0.00..22666.52 rows=96 width=372)

--